As you requested, the following Stored Procedure returns a count of how many routes it finds.
If the input parameter contains an invalid route (one that does not exist in the table), the count is unreliable.
With your input parameter, the following Stored Procedure yields a count of 3
'1001-COAKPL-DL_VSP-FC_RU2-HYDRC-CN_TRU-VSP-COSKLM-HYDRC-1001'
VERSION 1 - ROUTECOUNT
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mike Meinz
-- Create date: 2 May 2013
-- Description:
-- =============================================
CREATE PROCEDURE RouteCount
@P1 varchar(2048) = ''
AS
BEGIN
Declare @intCount int=0;
Declare @idx int=0;
Declare @loop bit=1;
Declare @x int=0;
Declare @y int=0;
Declare @intPresent int=0;
Declare @strTest varchar(2048);
Declare @strKey varchar(2048);
Declare @strKeyPrefix varchar(2048);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Set @strTest=@p1 + '-...';
Set @strKeyPrefix='';
While @loop=1
BEGIN
Set @x=CharIndex('-',@strTest);
Set @strKey=SUBSTRING (@strtest,1,@x-1);
SELECT @intPresent=COUNT(*) FROM ROUTES WHERE
ROUTESUMMARY=@strKeyPrefix + @strKey;
IF @intPresent=1
BEGIN
SET @strKeyPrefix= @strKey + '-';
SET @IntCount=@IntCount + 1;
END
ELSE
BEGIN
Set @strKeyPrefix=@strKeyPrefix + @strKey + '-';
END
Set @strTest=SUBSTRING(@StrTest,@x+1,LEN(@strTest)-@x);
If @strTest='...'
BEGIN
Set @loop=0
END
END
SELECT @intCount
END
GO
The Routes table contains this data:
RouteID RouteSummary
U1_KZ_U1 1001-COAKPL-DL_VSP-FC_RU2-HYDRC
U1_VJW_U2 HYDRC-CN_TRU-VSP-COSKLM-HYDRC
r5 HYDRC-1001
__________________________________________________________________________________________
VERSION 2 - GETROUTEIDS
This is a modification to the Stored Procedure to return the actual RouteIds in the order that they appear within the input parameter. Once again, if there is a route within the input string that does not exist in the database table, the rows returned will be unreliable.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mike Meinz
-- Create date: 3 May 2013
-- Description:
-- =============================================
CREATE PROCEDURE GetRouteIds
@P1 varchar(2048) = ''
AS
BEGIN
Declare @intCount int=0;
Declare @idx int=0;
Declare @loop bit=1;
Declare @x int=0;
Declare @y int=0;
Declare @intPresent int=0;
Declare @strTest varchar(2048);
Declare @strKey varchar(2048);
Declare @strKeyPrefix varchar(2048);
Declare @tb Table (RouteID varchar(50),routesequence int);
Declare @routeid varchar(50);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Append an end of input signal '...' to the end of the input string
Set @strTest=@p1 + '-...';
Set @strKeyPrefix='';
While @loop=1
BEGIN
--Locate the end of a route segment in the input string
Set @x=CharIndex('-',@strTest);
--Extract the route segment from the input string
Set @strKey=SUBSTRING (@strtest,1,@x-1);
Set @routeid=null;
SELECT @routeid=routeid FROM ROUTES WHERE
ROUTESUMMARY=@strKeyPrefix + @strKey;
-- Did we find a RouteId?
IF NOT @routeid is null
BEGIN
--Save last route segment so it can be used as start of next routesummary
SET @strKeyPrefix=@strKey + '-'
--Count the number of routeids found
SET @IntCount=@IntCount + 1;
--Insert into temporary table
INSERT INTO @tb (routeid,routesequence) values(@routeid,@intCount);
END
ELSE
BEGIN
--Append route segment
Set @strKeyPrefix=@strKeyPrefix + @strKey + '-';
END
--Remove the last route segment used from the start of the input string
Set @strTest=SUBSTRING(@StrTest,@x+1,LEN(@strTest)-@x);
--Test for the end of the input string
If @strTest='...'
BEGIN
Set @loop=0
END
END
SELECT routeid from @tb order by routesequence;
END
GO
The results from the GetRouteIds Stored Procedure:
routeid
U1_KZ_U1
U1_VJW_U2
r5