Loading ...

SQL: How to split comma separated string | CodeAsp.Net

SQL: How to split comma separated string

 /5
0 (0votes)

In this blog I will show how to split comma separated string or text in SQL. For this I have used user defined split function. It accepts two input parameters. The first parameter is the input text and the second one is the delimiter (It can be ',' , '|', ':' e.t.c.) The output of this function is table itself. So it can be used to insert comma separated values into the target table. Let's start with user defined function. Here is the script:

CREATE FUNCTION [fn_Split]
    (
      @text VARCHAR(MAX),
      @delimiter VARCHAR(20) = ' '
    )
RETURNS @Strings TABLE
    (
      [position] INT IDENTITY
                     PRIMARY KEY,
      [value] VARCHAR(100)
    )
AS BEGIN
    DECLARE @index INT
    SET @index = -1
    WHILE ( LEN(@text) > 0 ) 
        BEGIN -- Find the first delimiter
            SET @index = CHARINDEX(@delimiter, @text)
--No delimiter left?
--Insert the remaining @text and break the loop
            IF ( @index = 0 )
                AND ( LEN(@text) > 0 ) 
                BEGIN
                    INSERT  INTO @Strings
                    VALUES  (
                              CAST(@text AS VARCHAR(100)) 
                            )
                    BREAK
                END
--	Found a delimiter
--	Insert left of the delimiter and truncate the @text
            IF ( @index > 1 ) 
                BEGIN
                    INSERT  INTO @Strings
                    VALUES  (
                              CAST(LEFT(@text, @index - 1) AS VARCHAR(100)) 
                            )
                    SET @text = RIGHT(@text, ( LEN(@text) - @index ))
                END --Delimiter is 1st position = no @text to insert
            ELSE 
                SET @text = CAST(RIGHT(@text, ( LEN(@text) - @index )) AS VARCHAR(100))
        END
    RETURN
   END

Go

 


Let's use this function and do some tests. I will show you two examples here. 

First:

DECLARE @InputString AS VARCHAR(50)
SET @InputString = 'Abc,Def,Ghi,Jkl'
SELECT  *
FROM    [dbo].[fn_Split](@InputString, ',')

Here is the output:


Second:

DECLARE @TargetTable TABLE
    (
      [ID] INT IDENTITY,
      [Value] VARCHAR(20)
    )

DECLARE @InputString AS VARCHAR(50)
SET @InputString = 'Abc,Def,Ghi,Jkl'

--Let's insert it
INSERT  INTO @TargetTable
        SELECT  [Value]
        FROM    [dbo].[fn_Split](@InputString, ',')

--Let's see the target table
SELECT * FROM @TargetTable
Here is the output:



Do let me know your feedback, comments.

Comments (2)

   
vivek_iit
I just did a google search on your blog entry title and noticed that codeasp.net is at the top. I would suggest you post original content here and link it to your blog because codeasp.net is highly search engine optimized.
9/18/2009
 · 
by
   
rtpHarry
I tried to duplicate your tests but the UK version of google isn't show code asp.net as top unless I put speech marks around it.

Of course, that could be something to do that my blog isn't indexed in Google yet
9/22/2009
 · 
by

Top Posts