Splits a delimited string into a table

,

A user defined function to return a table af tokens from a delimited string.

Uses text variable so string can be of unlimited length, performs better than a list of xml elements.

Now supports returning of tokens by position

IF EXISTS (SELECT 1 FROM sysobjects WHERE name='SplitTokenStringPositions')
  DROP FUNCTION dbo.SplitTokenStringPositions 
GO
CREATE FUNCTION dbo.SplitTokenStringPositions (	
/*******************************************************************************

  Written By  : Simon Sabin
  Date        : 12 October 2002
  Description : Returns the start and length of the tokens in a string

  History
  Date       Change
  ------------------------------------------------------------------------------
  12/10/2002 Created
  15/10/2002 Fixed bug with spliting and enhanced to allow for return of positions
*******************************************************************************/
  @TokenString  text
 ,@Delimiter    varchar(100) )
RETURNS @tab TABLE (position smallint, start smallint, length smallint)
AS
BEGIN
	DECLARE 
	@Index          smallint,
	@Start          smallint,
	@DelimiterSize  smallint,
  @Finish         bit,
  @Position       smallint
	SET @DelimiterSize	= LEN(@Delimiter)

	SET @Start = 1
  SET @Index = 1
  SET @Finish = 0  
  SET @Position =1
  WHILE @Finish = 0
	BEGIN
		SET @Index = CHARINDEX(@Delimiter, @TokenString,@Start)
		IF @Index = 0
      BEGIN
      SET @Index = DATALENGTH(@TokenString) + 1
      SET @Finish = 1
      END

		INSERT INTO @tab VALUES(@position, @Start ,@Index  - @Start)

    SET @Start = @Index + @DelimiterSize
    SET @Position = @Position + 1
	END
  RETURN
END 
GO
IF exists (SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[SplitTokenString]') )
DROP FUNCTION [dbo].[SplitTokenString]
GO
CREATE FUNCTION dbo.SplitTokenString (	
/*******************************************************************************

  Written By  : Simon Sabin
  Date        : 12 October 2002
  Description : Splits a token string into its tokens
                Uses another function to obtain the start and end
                positions of each token

  History
  Date       Change
  ------------------------------------------------------------------------------
  12/10/2002 Created

*******************************************************************************/
    @TokenString 		text,
		@Delimiter			varchar(100) )
RETURNS TABLE 
AS
  RETURN (
   SELECT position, SUBSTRING(@TokenString, Start ,Length) "token"
	   FROM dbo.SplitTokenStringPositions(@TokenString ,@Delimiter))
GO

Rate

Share

Share

Rate