UFN_STRING_PARSER

Allow to parse any given string with any given delimiter

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@Delimiter	char(1)	IN
@String	varchar(max)	IN

Definition

Copy



create function dbo.UFN_STRING_PARSER(
  @Delimiter AS CHAR(1)=',', 
  @String    AS VARCHAR(MAX)
 )
returns table
as 
return
( 
  with [Numbers](num)
  as
  (   
        SELECT 0
        UNION ALL
        SELECT ROW_NUMBER() OVER(ORDER BY [id]) as num FROM sys.syscolumns
  )
  -- Split given string using given delimiter

  ,[Split](Num, String)
  AS
  (
        SELECT 
              Num
              ,SUBSTRING
              (
                    @String,
                    Num,
                    CASE CHARINDEX(@Delimiter, @String, Num)
                          WHEN 0 THEN LEN(@String) - Num + 1
                          ELSE CHARINDEX(@Delimiter, @String, Num) - Num
                          END
              ) AS String
        FROM  [Numbers]
        WHERE Num <= LEN(@String)
        AND         (SUBSTRING(@String, Num - 1, 1) = @Delimiter OR Num = 0)
  )


      SELECT
                   ROW_NUMBER() OVER (ORDER BY Num) AS [id]
                  ,RTRIM(LTRIM([String])) AS [Item]
      FROM  [Split]
      WHERE String <> ''
)