Most of us have encountered situations like splitting delimited string in a column of a table in SQL SERVER. The very first idea we get is to build a TABLE VALUED function which accepts two parameters, delimited string and delimiter as inputs, and returns a result set which contains the splitted string as rows. The implementation is like loop through the delimited string based on the delimiter index , slice the each sub string followed by delimiter and insert the slice into the temporary table till the delimited string length becomes ‘0’. After that return the temporary table. Here is the sample code inside the function.
CREATEFUNCTIONSplit(@DelimitedStringVARCHAR(MAX),@DelimiterCHAR(1))
RETURNS@resultTABLE(ValueVARCHAR(8000))
AS
BEGIN
-- Variable declaration
DECLARE@slicevarchar(8000)
DECLARE@idxINT=CHARINDEX(@Delimiter,@DelimitedString)
-- Initialization of index variable to ensure to execute the loop atleast once
SELECT@idx= 1
-- Check for empty string
IFLEN(@DelimitedString)0)
-- Insert the variable value in to the temporary table
INSERTINTO@result(Value)VALUES(@slice)
-- trim the Delimited string ie remove the sliced string from it
SET@DelimitedString=RIGHT(@DelimitedString,LEN(@DelimitedString)-@idx)
-- Check the length of the Delimited string
IFLEN(@DelimitedString)= 0
-- Break the loop
BREAK
END
-- Return the result set
RETURN
END
To execute the above funtion
SELECT*FROMdbo.Split('Asp.net,DNN,Java,PHP,Coldfusion,Servoy,FileMaker,Lasso,Sharepoint',',')
We can see the result as...
1 Asp.net
2 DNN
3 Java
4 PHP
5 Coldfusion
6 Servoy
7 FileMaker
8 Lasso
9 Sharepoint
This function works fine and we can use this function any where in our query. But we can do it better than this using XQUERY in SQL SERVER.
CREATEFUNCTIONXquerySplit(@DelimitedStringVARCHAR(MAX),@DelimiterCHAR(1))
RETURNS@resultTABLE(ValueVARCHAR(MAX))
AS
BEGIN
-- Convert the Delimited string in to XML type
DECLARE@stringXMLXML=CAST(''+REPLACE(@DelimitedString,@Delimiter,'')+''AsXML)
-- Insert the result set into the temporary table
INSERTINTO@result SELECTx.i.value('.','VARCHAR(MAX)') [email protected]('//i')x(i)
-- Return the temporary table
RETURN
END
If we run this statement
SELECT*FROM dbo.XquerySplit('Asp.net,DNN,Java,PHP,Coldfusion,Servoy,FileMaker,Lasso,Sharepoint',',')
we will get the same result as previous split function.
1 Asp.net
2 DNN
3 Java
4 PHP
5 Coldfusion
6 Servoy
7 FileMaker
8 Lasso
9 Sharepoint
If we want to run a SQL statement directly on the table instead of writing a table valued function, we can do the same as
-- Common table expression to convert each value of the column in to XML format
WITHSplitCTEAS (
SELECTCAST(''+REPLACE(DelimitedColumn,',','')+''ASXML)AS Strings
FROMTableName
)
-- Xquery to get the desired result set using CROSS JOIN
SELECTx.i.value('.','VARCHAR(MAX)')ASStrings
FROMSplitCTE
CROSSAPPLYStrings.nodes('//i')x(i)