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', ';')
