By Michael Stum


2011-03-30 23:03:11 8 Comments

I have a SQL Table like this:

| SomeID         | OtherID     | Data
+----------------+-------------+-------------------
| abcdef-.....   | cdef123-... | 18,20,22
| abcdef-.....   | 4554a24-... | 17,19
| 987654-.....   | 12324a2-... | 13,19,20

is there a query where I can perform a query like SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......' that returns individual rows, like this:

| OtherID     | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19

Basically split my data at the comma into individual rows?

I am aware that storing a comma-separated string into a relational database sounds dumb, but the normal use case in the consumer application makes that really helpful.

I don't want to do the split in the application as I need paging, so I wanted to explore options before refactoring the whole app.

It's SQL Server 2008 (non-R2).

14 comments

@Spider 2019-02-13 07:18:37

You can use the following function to extract data

CREATE FUNCTION [dbo].[SplitString]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END

    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END

@Sean Lange 2019-03-12 18:51:22

Using a while loop inside a multistatement table valued function is just about the worst way possible to split strings. There are so many set based options on this question already.

@Dungeon 2019-01-07 06:27:09

Very late but try this out:

SELECT ColumnID, Column1, value  --Do not change 'value' name. Leave it as it is.
FROM tbl_Sample  
CROSS APPLY STRING_SPLIT(Tags, ','); --'Tags' is the name of column containing comma separated values

So we were having this: tbl_Sample :

ColumnID|   Column1 |   Tags
--------|-----------|-------------
1       |   ABC     |   10,11,12    
2       |   PQR     |   20,21,22

After running this query:

ColumnID|   Column1 |   value
--------|-----------|-----------
1       |   ABC     |   10
1       |   ABC     |   11
1       |   ABC     |   12
2       |   PQR     |   20
2       |   PQR     |   21
2       |   PQR     |   22

Thanks!

@Jag Kandasamy 2018-08-22 02:56:26

Please refer below TSQL. STRING_SPLIT function is available only under compatibility level 130 and above.

TSQL:

DECLARE @stringValue NVARCHAR(400) = 'red,blue,green,yellow,black'  
DECLARE @separator CHAR = ','

SELECT [value]  As Colour
FROM STRING_SPLIT(@stringValue, @separator); 

RESULT:

Colour

red blue green yellow black

@RichardTheKiwi 2011-03-30 23:18:05

You can use the wonderful recursive functions from SQL Server:


Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
)

INSERT Testdata SELECT 1,  9, '18,20,22'
INSERT Testdata SELECT 2,  8, '17,19'
INSERT Testdata SELECT 3,  7, '13,19,20'
INSERT Testdata SELECT 4,  6, ''
INSERT Testdata SELECT 9, 11, '1,2,3,4'

The query

;WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)

SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

@Aliostad 2011-03-30 23:20:09

+1 That is my answer but with query itself as well!

@ca9163d9 2012-02-21 23:58:11

The code doesn't work if change the data type of the column Data from varchar(max) to varchar(4000), e.g. create table Testdata(SomeID int, OtherID int, Data varchar(4000))?

@RichardTheKiwi 2012-02-22 08:35:36

@NickW this may be because the parts before and after UNION ALL return different types from the LEFT function. Personally I don't see why you wouldn't jump to MAX once you get to 4000...

@Jagadeesh G 2013-07-27 05:59:47

@RichardTheKiwi this example is very useful for me thanks

@dsz 2014-01-14 05:19:00

For a BIG set of values, this can overrun the recursion limits for CTEs.

@RichardTheKiwi 2014-01-14 20:50:51

@dsz That's when you use OPTION (maxrecursion 0)

@RubyHaus 2015-03-20 14:04:48

@RichardTheKiwi - this is perfect, exactly what I needed. Impressive solution!

@Daniel 2015-07-09 06:29:39

@RichardTheKiwi - legend!

@smoore4 2016-07-15 15:27:39

The LEFT functions may need a CAST to work....for example LEFT(CAST(Data AS VARCHAR(MAX))....

@Andrew Steitz 2017-05-03 17:22:45

Wish I could upvote more than once!

@John 2017-08-08 16:23:37

Can i use this result as value of IN() ?

@RichardTheKiwi 2017-08-10 23:14:09

@Fawel Describe what you actually need and what you are starting from in a new question. Someone will answer more contextually

@John 2017-08-11 01:01:32

select * from t1 where IN(comma separated value from another table). can I use comma separated result into IN?

@timtom 2019-02-22 11:35:25

POSTGRESQL - if you need this query in postgresql: -- WITH -> WITH RECURSIVE -- + -> || -- CHARINDEX -> POSITION -- STUFF($a, $b, $c, $d) -> OVERLAY($a PLACING $d FROM $b FOR $c) working perfectly.

@mr R 2018-01-31 13:22:01

Function

CREATE FUNCTION dbo.SplitToRows (@column varchar(100), @separator varchar(10))
RETURNS @rtnTable TABLE
  (
  ID int identity(1,1),
  ColumnA varchar(max)
  )
 AS
BEGIN
    DECLARE @position int = 0
    DECLARE @endAt int = 0
    DECLARE @tempString varchar(100)

    set @column = ltrim(rtrim(@column))

    WHILE @position<=len(@column)
    BEGIN       
        set @endAt = CHARINDEX(@separator,@column,@position)
            if(@endAt=0)
            begin
            Insert into @rtnTable(ColumnA) Select substring(@column,@position,len(@column)[email protected])
            break;
            end
        set @tempString = substring(ltrim(rtrim(@column)),@position,@[email protected])

        Insert into @rtnTable(ColumnA) select @tempString
        set @[email protected]+1;
    END
    return
END

Use case

select * from dbo.SplitToRows('T14; p226.0001; eee; 3554;', ';')

Or just a select with multiple result set

DECLARE @column varchar(max)= '1234; 4748;abcde; 324432'
DECLARE @separator varchar(10) = ';'
DECLARE @position int = 0
DECLARE @endAt int = 0
DECLARE @tempString varchar(100)

set @column = ltrim(rtrim(@column))

WHILE @position<=len(@column)
BEGIN       
    set @endAt = CHARINDEX(@separator,@column,@position)
        if(@endAt=0)
        begin
        Select substring(@column,@position,len(@column)[email protected])
        break;
        end
    set @tempString = substring(ltrim(rtrim(@column)),@position,@[email protected])

    select @tempString
    set @[email protected]+1;
END

@Sean Lange 2019-03-12 18:51:11

Using a while loop inside a multistatement table valued function is just about the worst way possible to split strings. There are so many set based options on this question already.

@Arun Pratap Singh 2017-10-06 10:21:00

Below works on sql server 2008

select *, ROW_NUMBER() OVER(order by items) as row# 
from 
( select 134 myColumn1, 34 myColumn2, 'd,c,k,e,f,g,h,a' comaSeperatedColumn) myTable
    cross apply 
SPLIT (rtrim(comaSeperatedColumn), ',') splitedTable -- gives 'items'  column 

Will get all Cartesian product with the origin table columns plus "items" of split table.

@Pரதீப் 2016-03-30 10:01:30

Finally, the wait is over with SQL Server 2016. They have introduced the Split string function, STRING_SPLIT:

select OtherID, cs.Value --SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

All the other methods to split string like XML, Tally table, while loop, etc.. have been blown away by this STRING_SPLIT function.

Here is an excellent article with performance comparison: Performance Surprises and Assumptions: STRING_SPLIT.

For older versions, using tally table here is one split string function(best possible approach)

CREATE FUNCTION [dbo].[DelimitedSplit8K]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Referred from Tally OH! An Improved SQL 8K “CSV Splitter” Function

@Syed Md. Kamruzzaman 2017-03-06 06:21:49

very important answer

@Stewart 2017-04-28 16:12:16

I would use STRING_SPLIT if only the server were on SQL Server 2016! BTW according to the page you've linked to, the field name it outputs is value, not SplitData.

@dsz 2014-01-14 06:20:01

As of Feb 2016 - see the TALLY Table Example - very likely to outperform my TVF below, from Feb 2014. Keeping original post below for posterity:


Too much repeated code for my liking in the above examples. And I dislike the performance of CTEs and XML. Also, an explicit Id so that consumers that are order specific can specify an ORDER BY clause.

CREATE FUNCTION dbo.Split
(
    @Line nvarchar(MAX),
    @SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Data nvarchar(100) NOT NULL
)
AS
BEGIN
    IF @Line IS NULL RETURN

    DECLARE @split_on_len INT = LEN(@SplitOn)
    DECLARE @start_at INT = 1
    DECLARE @end_at INT
    DECLARE @data_len INT

    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @[email protected]_at END
        INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len
    END

    RETURN
END

@Eske Rahn 2016-04-02 01:57:17

Nice to see that it have been solved in the 2016 version, but for all of those that is not on that, here are two generalized and simplified versions of the methods above.

The XML-method is shorter, but of course requires the string to allow for the xml-trick (no 'bad' chars.)

XML-Method:

create function dbo.splitString(@input Varchar(max), @Splitter VarChar(99)) returns table as
Return
    SELECT Split.a.value('.', 'VARCHAR(max)') AS Data FROM
    ( SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data 
    ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

Recursive method:

create function dbo.splitString(@input Varchar(max), @Splitter Varchar(99)) returns table as
Return
  with tmp (DataItem, ix) as
   ( select @input  , CHARINDEX('',@Input)  --Recu. start, ignored val to get the types right
     union all
     select Substring(@input, ix+1,ix2-ix-1), ix2
     from (Select *, CHARINDEX(@Splitter,@[email protected],ix+1) ix2 from tmp) x where ix2<>0
   ) select DataItem from tmp where ix<>0

Function in action

Create table TEST_X (A int, CSV Varchar(100));
Insert into test_x select 1, 'A,B';
Insert into test_x select 2, 'C,D';

Select A,data from TEST_X x cross apply dbo.splitString(x.CSV,',') Y;

Drop table TEST_X

XML-METHOD 2: Unicode Friendly 😀 (Addition courtesy of Max Hodges) create function dbo.splitString(@input nVarchar(max), @Splitter nVarchar(99)) returns table as Return SELECT Split.a.value('.', 'NVARCHAR(max)') AS Data FROM ( SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

@jpaugh 2016-08-31 21:26:52

This may seem obvious, but how do you use these two functions? Especially, can you show how to use it in the OP's use case?

@Eske Rahn 2016-09-09 12:50:53

Here is a quick example: Create table TEST_X (A int, CSV Varchar(100)); Insert into test_x select 1, 'A,B'; Insert into test_x select 2, 'C,D'; Select A,data from TEST_X x cross apply dbo.splitString(x.CSV,',') Y; Drop table TEST_X

@Nitin Badole 2019-01-18 10:30:47

This is exactly what I needed! Thank you.

@tommylux 2015-03-16 16:40:25

When using this approach you have to make sure that none of your values contains something that would be illegal XML – user1151923

I always use the XML method. Make sure you use VALID XML. I have two functions to convert between valid XML and Text. (I tend to strip out the carriage returns as I don't usually need them.

CREATE FUNCTION dbo.udf_ConvertTextToXML (@Text varchar(MAX)) 
    RETURNS varchar(MAX)
AS
    BEGIN
        SET @Text = REPLACE(@Text,CHAR(10),'')
        SET @Text = REPLACE(@Text,CHAR(13),'')
        SET @Text = REPLACE(@Text,'<','&lt;')
        SET @Text = REPLACE(@Text,'&','&amp;')
        SET @Text = REPLACE(@Text,'>','&gt;')
        SET @Text = REPLACE(@Text,'''','&apos;')
        SET @Text = REPLACE(@Text,'"','&quot;')
    RETURN @Text
END


CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX)) 
    RETURNS VARCHAR(max)
AS
    BEGIN
        SET @Text = REPLACE(@Text,'&lt;','<')
        SET @Text = REPLACE(@Text,'&amp;','&')
        SET @Text = REPLACE(@Text,'&gt;','>')
        SET @Text = REPLACE(@Text,'&apos;','''')
        SET @Text = REPLACE(@Text,'&quot;','"')
    RETURN @Text
END

@Stewart 2017-05-01 09:30:48

There's a small problem with the code you've got there. It'll change '<' to '&amp;lt;' instead of '&lt;' like it should. So you need to encode '&' first.

@Shnugo 2018-07-19 09:02:10

There's no need for such a function... Just use the implicit abilities. Try this out: SELECT (SELECT '<&> blah' + CHAR(13)+CHAR(10) + 'next line' FOR XML PATH(''))

@user3245135 2014-01-28 15:29:45

select t.OtherID,x.Kod
    from testData t
    cross apply (select Code from dbo.Split(t.Data,',') ) x

@tobriand 2015-11-03 13:45:55

Does exactly what I was after, and easier to read than many of the other examples (provided there's already a function in the DB for delimited string split). As someone not previously familiar with CROSS APPLY, that's kinda useful!

@Jayendran 2017-06-07 15:29:53

I couldn't understand this part (select Code from dbo.Split(t.Data,',') ) ? dbo.Split is an table where is this exist and also Code is the Column in Split table ? i couldn't find the list of those table or values in anywhere in this Page ?

@Akbar Kautsar 2017-08-11 04:07:38

My working code are: select t.OtherID, x.* from testData t cross apply (select item as Data from dbo.Split(t.Data,',') ) x

@Jayvee 2013-11-07 13:22:44

DECLARE @id_list VARCHAR(MAX) = '1234,23,56,576,1231,567,122,87876,57553,1216'
DECLARE @table TABLE ( id VARCHAR(50) )
DECLARE @x INT = 0
DECLARE @firstcomma INT = 0
DECLARE @nextcomma INT = 0

SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1 -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
                              THEN LEN(@id_list) + 1
                              ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
                         END
        INSERT  INTO @table
        VALUES  ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) )
        SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1)
        SET @x = @x - 1
    END

SELECT  *
FROM    @table

@Aaron Schultz 2017-10-16 20:06:19

This is one of the few methods that works with the limited SQL support in Azure SQL Data Warehouse.

@bvr 2013-04-18 12:33:11

Check this

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

@user1151923 2015-03-04 18:22:22

When using this approach you have to make sure that none of your values contains something that would be illegal XML

@Control 2015-07-08 16:46:48

This is great. Can I ask you, how would I rewrite that if I wanted the new column to only show the first character from my split string?

@chazbot7 2017-01-06 00:46:47

This worked perfectly, thank you! I had to update the VARCHAR limit but it worked perf after that.

@Chris Brickhouse 2018-04-08 19:20:25

this worked perfectly for sql 2014. thanks!

@Klix Media 2012-07-28 12:58:24

;WITH tmp(SomeID, OtherID, DataItem, Data) as (
    SELECT SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
FROM Testdata
WHERE Data > ''
)
SELECT SomeID, OtherID, Data
FROM tmp
ORDER BY SomeID

with only tiny little modification to above query...

@Leigh 2012-07-28 20:50:43

Can you briefly explain how this is an improvement over the version in the accepted answer?

@TamusJRoyce 2015-01-05 16:24:40

No union all...less code. Since it is using union all instead of union, shouldn't be a performance difference?

@Oedhel Setren 2015-02-27 15:20:30

This didn't return all the rows it should have. I'm not sure what about the data requires the union all, but your solution returned the same number of rows as the original table.

@Eske Rahn 2016-04-07 22:38:21

(the problem here is that the recursive part is the one omitted...)

Related Questions

Sponsored Content

20 Answered Questions

76 Answered Questions

[SOLVED] How do I iterate over the words of a string?

  • 2008-10-25 08:58:21
  • Ashwin Nanjappa
  • 2108890 View
  • 2830 Score
  • 76 Answer
  • Tags:   c++ string split

45 Answered Questions

34 Answered Questions

[SOLVED] How do I split a string on a delimiter in Bash?

13 Answered Questions

[SOLVED] Best way to get identity of inserted row?

  • 2008-09-03 21:32:02
  • Oded
  • 750820 View
  • 999 Score
  • 13 Answer
  • Tags:   sql sql-server tsql

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

23 Answered Questions

[SOLVED] How to convert comma-separated String to ArrayList?

37 Answered Questions

[SOLVED] How can I remove duplicate rows?

1 Answered Questions

3 Answered Questions

Turning string into rows

  • 2015-07-02 10:48:01
  • CB Du Rietz
  • 360 View
  • 0 Score
  • 3 Answer
  • Tags:   sql-server tsql

Sponsored Content