Sql Stored procedure with split function

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