How to use STRING_SPLIT() Function or XML Method to Split a String or Text into Multiple Rows based on a Delimiter in SQL Server

How to Split a String or Text into Multiple Rows based on a Delimiter using STRING_SPLIT () Function and XML Method in SQL Server

In SQL Server, we often need to split the String or Text, based on delimiters. We can do this using STRING_SPLIT () Function or XML Method as discussed below.

1) Using STRING_SPLIT () Function :
In SQL Server 2016, Microsoft introduced the STRING_SPLIT() inbuilt function to split a string using a specific delimiter. This is the easiest method to split delimited string in SQL Server. To use STRING_SPLIT, the database should be at least in Compatibility level of 130(i.e, SQL Server 2016.)
Syntax:

STRING_SPLIT(StrInput, Delimitor)

Example:

Now will see how we can split a String with different delimiters(like comma, space, pipe..etc).
DECLARE @strInput1 VARCHAR(2000);

DECLARE @strInput2 VARCHAR(2000);
DECLARE @strInput3 VARCHAR(2000);

SET @strInput1 = 'Sunday,01,Tuesday,30,Thursday,04,Saturday'
SET @strInput2 = 'Sunday 01 Tuesday,30,Thursday 04 Saturday'

SET @strInput3 = 'Sunday|01|Tuesday,30,Thursday|04|Saturday'

Select Value As sValues FROM STRING_SPLIT(@strInput1, ',')
Result:

Select Value As sValues FROM STRING_SPLIT(@strInput2, ' ')

Result:

Select Value As sValues FROM STRING_SPLIT(@strInput3, '|')

Result:

Note:

Please note that the STRING_SPLIT() Function will work only for the SQL Server 2016 and its higher versions.

For the lower versions like SQL Server-2008, 2012, 2014.. we can use below XML Method.

2) Using the XML Method :
Declare @str Varchar(4000),
@sXML AS XML

Set @str ='Sunday|01|Tuesday,30,Thursday|04|Saturday'
Set @sXML='<root><i>'+REPLACE(@str,'|','</i><i>')+'</i></root>'

Select LTRIM(RTRIM([i].value('.','varchar(4000)'))) As StrValues
From @sXML.nodes('/root/i') As [Items]([i])

                                                    

3) Using a User defined Function with XML Method :

We can create user defined function using the XML Method to Split a String based on the specified delimiter as follows..
CREATE FUNCTION [udf_SplitString]
  (
    @SrcString VARCHAR(4000),
    @Delim VARCHAR(25)=','
   )
 RETURNS @ResultTable TABLE
  (
    [strID] INT IDENTITY(1,1),
    [sValues] VARCHAR(4000)
   )
AS

BEGIN
    DECLARE @w_xml xml; 
    SET @w_xml=N'<root><i>' + Replace(@SrcString,@Delim,'</i><i>')+'</i></root>';

  INSERT INTO @ResultTable ([sValues])
SELECT
LTRIM(RTRIM([i].value('.', 'VARCHAR(4000)'))) As Value
FROM
@w_xml.nodes('//root/i') AS [Items]([i])
Where LTRIM(RTRIM([i].value('.', 'VARCHAR(4000)')))<>''
RETURN
END
 GO

Now we will check how this function splits the strings based on the specified delimiter.
Select * from udf_SplitString('Sunday,01,Tuesday,30,Thursday,04,Saturday',',');
Result:

Select * from udf_SplitString('Sunday 01 Tuesday,30,Thursday 04 Saturday', ' ');
Result:

Select * from udf_SplitString('Sunday|01 Tuesday|30,Thursday |04;Saturday', '|');
Result:

Select * from udf_SplitString('Sunday|01 Tuesday||30,Thursday |||04;Saturday', '|');
Result:

Notes:

We can check the compatibility of our database(s) using the below Query.
Select Name As dbName, Compatibility_level , Version_Name =
CASE Compatibility_level
           WHEN 65 THEN 'SQL Server 6.5'
           WHEN 70 THEN 'SQL Server 7.0'
           WHEN 80 THEN 'SQL Server 2000'
           WHEN 90 THEN 'SQL Server 2005'
           WHEN 100 THEN 'SQL Server 2008/R2'
           WHEN 110 THEN 'SQL Server 2012'
           WHEN 120 THEN 'SQL Server 2014'
           WHEN 130 THEN 'SQL Server 2016'
           WHEN 140 THEN 'SQL Server 2017'
     ELSE 'New/Unknown - '+CONVERT(varchar(10),Compatibility_level) 
END
From Sys.Databases

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------