Split comma separated string In SQL Server



           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 .

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




 

No comments:

Post a Comment