Split comma separated value using string_split In SQL



           In this short article I will share with an example, how to split and convert a comma separated / delimited string to a different column.We have to create function to split string using delimiter. here i am sharing simple way to split a string in SQL Server by casting data to XML.



DECLARE @TEMPTABLE AS TABLE
(
  Remarks VARCHAR(50)
)

INSERT INTO @TEMPTABLE
SELECT 'Amal,TravelCost,100'
UNION ALL
SELECT 'Arun,TravelCost,200'
UNION ALL
SELECT 'Manu,TravelCost,300'
UNION ALL
SELECT 'Vinu,TravelCost,400'

SELECT * FROM @TEMPTABLE

SELECT
CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[1]', 'varchar(128)') AS Customer,
CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[2]', 'varchar(128)') AS TransType,
CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[3]', 'varchar(128)') AS Amount FROM @TEMPTABLE





       In the above example first we cast the data to XML and split string to separate column by finding the position of XML closing tag.

      You can also split string using STRING_SPLIT function in SQL server .It is a table-valued built in function that splits a string into rows of sub strings, based on a specified separator character.

SELECT VALUE
FROM @TEMPTABLE
CROSS APPLY STRING_SPLIT(Remarks, ',')


      
     If input string contains two or more consecutive occurrences of the delimiter character. Empty sub strings are treated the same as are plain sub strings.


SELECT * FROM  STRING_SPLIT('a;;b', ';')



No comments:

Post a comment