Turning a Comma Separated string into individual rows

Question

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

User · Answer

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,@Input+@Splitter,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);

User · Answer

I know it has a lot of answers  but I want to write my version of split function like others and like string split SQL Server 2016 native function  create function  dbo   Split         Value nvarchar max        Delimiter nvarchar 50    returns  tbl table       Seq int primary key identity 1  1       Value nvarchar max    as begin     declare  Xml xml   cast   lt d gt     replace  Value   Delimiter    lt  d gt  lt d gt        lt  d gt   as xml       insert into  tbl              Value      select  a split value       nvarchar max    as Value     from     Xml nodes   d   a split           return end   Seq column is primary key to support fast join with other real table or Split function returned table  Used XML function to support large data  looping version will slow down significantly when you have large data   Here s a answer to question  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    select  t SomeID  t OtherID  s Value from    Testdata t         cross apply dbo Split t String       s  --Output SomeID  OtherID Value 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   Joining Split with other split declare  Names nvarchar max     a b c d  declare  Codes nvarchar max     10 20 30 40   select  n Seq  n Value Name  c Value Code from    dbo Split  Names       n         inner join dbo Split  Codes       c on n Seq   c Seq  --Output Seq Name    Code 1   a       10 2   b       20 3   c       30 4   d       40   Split two times declare  NationLocSex nvarchar max     Korea Seoul 1 Vietnam Kiengiang 0 China Xian 0     with rows as       select  Value     from    dbo Split  NationLocSex         select  rw Value r  cl Value c from    rows rw         cross apply dbo Split rw Value       cl  --Output r                       c Korea Seoul 1           Korea Korea Seoul 1           Seoul Korea Seoul 1           1 Vietnam Kiengiang 0     Vietnam Vietnam Kiengiang 0     Kiengiang Vietnam Kiengiang 0     0 China Xian 0            China China Xian 0            Xian China Xian 0            0   Split to columns declare  Numbers nvarchar 50     First Second Third     with t as       select  case when Seq   1 then Value end f1              case when Seq   2 then Value end f2              case when Seq   3 then Value end f3     from    dbo Split  Numbers         select  min f1  f1  min f2  f2  min f3  f3 from    t  --Output f1      f2      f3 First   Second  Third   Generate rows by range  declare  Ranges nvarchar 50     1-2 4-6   declare  Numbers table  Num int  insert into  Numbers values  1   2   3   4   5   6   7   8     with t as       select  r Seq  r Value              min case when ft Seq   1 then ft Value end  ValueFrom              min case when ft Seq   2 then ft Value end  ValueTo     from    dbo Split  Ranges       r             cross apply dbo Split r Value   -   ft     group by r Seq  r Value   select  t Seq  t Value  t ValueFrom  t ValueTo  n Num from    t         inner join  Numbers n on n Num between t ValueFrom and t ValueTo  --Output Seq Value   ValueFrom   ValueTo Num 1   1-2     1           2       1 1   1-2     1           2       2 2   4-6     4           6       4 2   4-6     4           6       5 2   4-6     4           6       6

User · Answer

WITH tmp SomeID  OtherID  DataItem  Data  as       SELECT SomeID  OtherID  LEFT Data  CHARINDEX     Data     -1           STUFF Data  1  CHARINDEX     Data           FROM Testdata WHERE Data  gt       SELECT SomeID  OtherID  Data FROM tmp ORDER BY SomeID   with only tiny little modification to above query

User · Answer

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

User · Answer

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  gt  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

User · Answer

select t OtherID x Kod     from testData t     cross apply  select Code from dbo Split t Data        x

User · Answer

Check this   SELECT A OtherID         Split a value       VARCHAR 100    AS Data    FROM           SELECT OtherID             CAST    lt M gt     REPLACE Data         lt  M gt  lt M gt        lt  M gt   AS XML  AS Data        FROM  Table1    AS A CROSS APPLY Data nodes    M   AS Split a

User · Answer

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

User · Answer

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  end at- start 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

User · Answer

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 lt  len  column      BEGIN                set  endAt   CHARINDEX  separator  column  position              if  endAt 0              begin             Insert into  rtnTable ColumnA  Select substring  column  position len  column - position              break              end         set  tempString   substring ltrim rtrim  column    position  endAt- position           Insert into  rtnTable ColumnA  select  tempString         set  position  endAt 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 lt  len  column  BEGIN            set  endAt   CHARINDEX  separator  column  position          if  endAt 0          begin         Select substring  column  position len  column - position          break          end     set  tempString   substring ltrim rtrim  column    position  endAt- position       select  tempString     set  position  endAt 1  END

User · Answer

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     amp lt            SET  Text   REPLACE  Text   amp     amp amp            SET  Text   REPLACE  Text   gt     amp gt            SET  Text   REPLACE  Text        amp apos            SET  Text   REPLACE  Text       amp quot        RETURN  Text END   CREATE FUNCTION dbo udf ConvertTextFromXML   Text VARCHAR MAX        RETURNS VARCHAR max  AS     BEGIN         SET  Text   REPLACE  Text   amp lt     lt            SET  Text   REPLACE  Text   amp amp     amp            SET  Text   REPLACE  Text   amp gt     gt            SET  Text   REPLACE  Text   amp apos                 SET  Text   REPLACE  Text   amp quot            RETURN  Text END

User · Answer

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  gt        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

User · Answer

By creating this function   DelimitedSplit   which splits a string  you could do an OUTER APPLY to your SELECT  CREATE FUNCTION  dbo   DelimitedSplit  --      Define I O parameters           pString VARCHAR 8000    pDelimiter CHAR 1   --WARNING    DO NOT USE MAX DATA-TYPES HERE   IT WILL KILL PERFORMANCE  RETURNS TABLE WITH SCHEMABINDING AS  RETURN --       quot Inline quot  CTE Driven  quot Tally Table quot  produces values from 1 up to 10 000         -- enough to cover VARCHAR 8000    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 INNER JOIN E1 b ON b N   a N   --10E 2 or 100 rows        E4 N  AS  SELECT 1 FROM E2 a INNER JOIN E2 b ON b N   a N   --10E 4 or 10 000 rows max  cteTally N  AS  --     This provides the  quot base quot  CTE and limits the number of rows right up front                      -- for both a performance gain and prevention of accidental  quot overruns quot                   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  quot element quot  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    TEST 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   SELECT    FROM  Testdata OUTER APPLY  dbo   DelimitedSplit  String       DROP TABLE  Testdata  RESULT SomeID  OtherID String      ItemNumber  Item 1       9       18 20 22    1           18 1       9       18 20 22    2           20 1       9       18 20 22    3           22 2       8       17 19       1           17 2       8       17 19       2           19 3       7       13 19 20    1           13 3       7       13 19 20    2           19 3       7       13 19 20    3           20 4       6       1    9       11      1 2 3 4     1           1 9       11      1 2 3 4     2           2 9       11      1 2 3 4     3           3 9       11      1 2 3 4     4           4

User · Answer

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

User · Answer

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

User · Answer

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 gt 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

[sql-server] Turning a Comma Separated string into individual rows

Examples related to sql-server

Examples related to tsql

Examples related to split

Examples related to comma