Split a string to a table using T-SQL

There is no built-in function to split a delimited string in Microsoft SQL Server to split a string to a table; we need to create our own function or sp or sql script. Converting a delimited string into a data set or transforming it into useful data can be an extremely useful tool when working with complex inputs or user-provided data. There are many methods available to accomplish this task; here we will discuss many of them, comparing performance, accuracy, and availability.

Using Table-Valued Function

Here I am creating a function which accepts the string which is separated by delimiter and delimiter value.

I am creating a function ‘Split_a_string_to_table’

CREATE FUNCTION Split_a_string_to_table (          @Str NVARCHAR(MAX),       @Delimiter NCHAR(1) ) RETURNS @Output TABLE (         List_Of_Item NVARCHAR(1000) ) AS BEGIN       DECLARE @StartIndex INT, @EndIndex INT       SET @StartIndex = 1       IF SUBSTRING(@Str, LEN(@Str) - 1, LEN(@Str)) <> @Delimiter       BEGIN             SET @Str = @Str + @Delimiter       END        WHILE CHARINDEX(@Delimiter, @Str) > 0       BEGIN             SET @EndIndex = CHARINDEX(@Delimiter, @Str)                    INSERT INTO @Output(List_Of_Item)             SELECT SUBSTRING(@Str, @StartIndex, @EndIndex - 1)             SET @Str = SUBSTRING(@Str, @EndIndex + 1, LEN(@Str))       END       RETURN END

Create this function

 

Now we need to use this function where we want. See the example.

I am passing the string as 'bagesh,kumar,singh,pune' and delimiter as ‘,’ (comma) see the output.

See the another example

I am passing the string as 'bagesh|kumar|singh|pune' and delimiter as ‘|’ (Pipe) see the output.

 

We can try any delimiter it is working fine.

Using XML Split a sting to table

Here a am creating a function which accept input as string and delimiter and it will return table value

CREATE FUNCTION Split_a_string_to_table_using_XML                 (                  @Str NVARCHAR(MAX),                  @Delimiter NCHAR(1)                  ) RETURNS  TABLE AS RETURN    SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS Data FROM     (                 SELECT CAST ('<A>' + REPLACE(@Str, @Delimiter, '</A><A>') + '</A>' AS XML) AS Data     ) AS A CROSS APPLY Data.nodes ('/A') AS Split(a);

I am using this function

I am passing the string as 'bagesh,kumar,singh,pune' and delimiter as ‘,’ (comma) see the output.

See the another example

I am passing the string as 'bagesh|kumar|singh|pune' and delimiter as ‘|’ (Pipe) see the output.