T-SQL function to convert comma separated values into rows of table

Below is the tabular function that
converts comma or semi colon separated values of a string into rows of a table.

CREATE FUNCTION [dbo].[FnGetSplitCommaSeperatedintoRows]

(@InputString NVARCHAR(MAX))

RETURNS
@StringTable TABLE (StringValue
NVARCHAR(MAX))

BEGIN

DECLARE
@String NVARCHAR(MAX)

SET @String = ‘,’ + @InputString + ‘,’

SET
@String = REPLACE(@String,‘;’,‘,’)

WHILE
LEN(@String) > 0

BEGIN

IF
LTRIM(RTRIM(SUBSTRING (@String,0,CHARINDEX(‘,’,@String))))<>‘,’

AND LTRIM(RTRIM(SUBSTRING (@String,0,CHARINDEX(‘,’,@String))))<>”

IF 0 = (SELECT COUNT(1) FROM @StringTable WHERE
StringValue =
SUBSTRING (@String , 0,CHARINDEX(‘,’,@String)))

INSERT @StringTable (StringValue)

SELECT
SUBSTRING ( @String , 0,CHARINDEX(‘,’,@String))

SET
@String = SUBSTRING ( @String , CHARINDEX(‘,’,@String)+1 , LEN(@String)-CHARINDEX(‘,’,@String))

END

RETURN

END

Result :

Related Posts