Home » , » split function in sql

split function in sql

Penulis : Sandeep Mittal on Monday, July 16, 2012 | 7/16/2012 11:32:00 AM

The purpose of split function (table valued function) is to split a delimited value into multiple values based on delimiter and display in tabular form.

split function takes two parameters
  • Delimited Value: The delimited value to be split in multiple values.
  • Delimiter: The delimiter like comma, colon, semicolon, pipe etc. on the basis of which delimited value to be split.
and returns table of multiple values delimited on the basis of delimiter.

CREATE FUNCTION split(
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END

Step wise explanation of split Function
  1. Converting the delimited value into XML using replace function, where all delimiters are getting replaced with tag to make it as XML and assigning the same to XML Variable
  2. Reading the Node from XML variable and storing the values in table variable @t. Refer related blog post, XML Node into Tabular Form
  3. Returning the table variable @t

Let's take an example to split a comma delimited value into multiple values on the basis of delimiter (comma) using split function.

SELECT * FROM dbo.split('val1,val2,val3', ',')

OUTPUT


Now let's take another example where you have multiple delimited value stored in a table against an ID and each value needs to split on the basis of delimiter.

We would be using Cross Apply clause in the example.

Refer related post, Apply Operator in SQL Server

DECLARE @TAB TABLE(
    id int, list varchar(100)
)
INSERT INTO @TAB
SELECT 1, 'apple;banana;grapes;orange'
UNION ALL SELECT 2, 'potato;onion;carrot;brinjal'

SELECT * FROM @TAB

SELECT    t.id, s.val
FROM    @TAB t
CROSS APPLY dbo.split(t.list, ';') s
OUTPUT
Share this article :

+ comments + 8 comments

September 26, 2012 at 3:10 PM

its really helpful.

November 25, 2013 at 1:22 PM

Hi
Found this article helpful in resolving imports from old system, what i need to know is if you can use something similar on multiple columns instead of just one

November 25, 2013 at 4:13 PM

Use split function for each list and join on id

November 29, 2013 at 1:08 PM

Hi one more question, I have a column with values "21:20~21:28~21:28~21:40~21:40~21:40~21:42" ie tilde delim, that I now need to split into separate columns for each value. Suggestions?

December 5, 2013 at 11:08 AM

Thank you, Louis...

Louis van Rooyen
July 25, 2015 at 10:14 AM

Than You, was hoping not to

July 25, 2015 at 10:14 AM

Hi,
This is only one value for the column. I guess, there would be many different values. How would
you generate columns for each value?


However after using split function you can use dynamic pivot to generate columns.
Refer http://bit.ly/12pEG93 for dynamic pivot.

Louis van Rooyen
July 25, 2015 at 10:14 AM

Hi, thank You, it seems to be the accepted way of doing it, very much appreciated

Post a Comment

 
Design Template by panjz-online | Support by creating website | Powered by Blogger