Turning a Comma Separated string into individual rowsSplit values over multiple rowsSQL Server 2014 - How to split a string with commas into multiple rows?How to get values separated by comma into new record in SQL Server?Merge multiple column having comma separated values to a single columnMySql - Create new rows from existing rows with comma separated string columnSQL Server: Split values from columns with multiple values, into multiple rowsSQL Seperate delimiter and create new rowHow can i pass an array as a parameter into a stored procedure?Sql query to create single row to multiple rowHow to get second, third and more values from string with fields separated by commas?How can I remove duplicate rows?Best way to get identity of inserted row?How to concatenate text from multiple rows into a single text string in SQL server?How do I iterate over the words of a string?Inserting multiple rows in a single SQL query?How do I split a string on a delimiter in Bash?What is the best way to auto-generate INSERT statements for a SQL Server table?How to convert comma-separated String to ArrayList?Turning string into rowsTurning related Comma Separated strings into individual rows with multiple columns

How do I rename a LINUX host without needing to reboot for the rename to take effect?

Why isn't KTEX's runway designation 10/28 instead of 9/27?

Visiting the UK as unmarried couple

Books on the History of math research at European universities

Lightning Web Component - do I need to track changes for every single input field in a form

Superhero words!

Why is delta-v is the most useful quantity for planning space travel?

How can a jailer prevent the Forge Cleric's Artisan's Blessing from being used?

Is there an Impartial Brexit Deal comparison site?

Are taller landing gear bad for aircraft, particulary large airliners?

Can I create an upright 7-foot × 5-foot wall with the Minor Illusion spell?

Hostile work environment after whistle-blowing on coworker and our boss. What do I do?

The One-Electron Universe postulate is true - what simple change can I make to change the whole universe?

For airliners, what prevents wing strikes on landing in bad weather?

Who must act to prevent Brexit on March 29th?

Did US corporations pay demonstrators in the German demonstrations against article 13?

Bob has never been a M before

Simple recursive Sudoku solver

I2C signal and power over long range (10meter cable)

Can somebody explain Brexit in a few child-proof sentences?

Can I use my Chinese passport to enter China after I acquired another citizenship?

Organic chemistry Iodoform Reaction

Golf game boilerplate

What does the "3am" section means in manpages?



Turning a Comma Separated string into individual rows


Split values over multiple rowsSQL Server 2014 - How to split a string with commas into multiple rows?How to get values separated by comma into new record in SQL Server?Merge multiple column having comma separated values to a single columnMySql - Create new rows from existing rows with comma separated string columnSQL Server: Split values from columns with multiple values, into multiple rowsSQL Seperate delimiter and create new rowHow can i pass an array as a parameter into a stored procedure?Sql query to create single row to multiple rowHow to get second, third and more values from string with fields separated by commas?How can I remove duplicate rows?Best way to get identity of inserted row?How to concatenate text from multiple rows into a single text string in SQL server?How do I iterate over the words of a string?Inserting multiple rows in a single SQL query?How do I split a string on a delimiter in Bash?What is the best way to auto-generate INSERT statements for a SQL Server table?How to convert comma-separated String to ArrayList?Turning string into rowsTurning related Comma Separated strings into individual rows with multiple columns













186















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).










share|improve this question




























    186















    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).










    share|improve this question


























      186












      186








      186


      76






      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).










      share|improve this 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).







      sql-server tsql split comma






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Mar 21 '18 at 5:41









      DineshDB

      3,95942239




      3,95942239










      asked Mar 30 '11 at 23:03









      Michael StumMichael Stum

      118k99362506




      118k99362506






















          14 Answers
          14






          active

          oldest

          votes


















          225














          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





          share|improve this answer

























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

            – Aliostad
            Mar 30 '11 at 23:20






          • 1





            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))?

            – ca9163d9
            Feb 21 '12 at 23:58







          • 4





            @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...

            – RichardTheKiwi
            Feb 22 '12 at 8:35






          • 3





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

            – RichardTheKiwi
            Jan 14 '14 at 20:50






          • 8





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

            – smoore4
            Jul 15 '16 at 15:27


















          102














          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






          share|improve this answer




















          • 7





            very important answer

            – Syed Md. Kamruzzaman
            Mar 6 '17 at 6:21











          • 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.

            – Stewart
            Apr 28 '17 at 16:12


















          79














          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);





          share|improve this answer


















          • 8





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

            – user1151923
            Mar 4 '15 at 18:22











          • 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?

            – Control
            Jul 8 '15 at 16:46











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

            – chazbot7
            Jan 6 '17 at 0:46











          • this worked perfectly for sql 2014. thanks!

            – Chris Brickhouse
            Apr 8 '18 at 19:20


















          16














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





          share|improve this answer




















          • 3





            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!

            – tobriand
            Nov 3 '15 at 13:45











          • 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 ?

            – Jayendran
            Jun 7 '17 at 15:29







          • 1





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

            – Akbar Kautsar
            Aug 11 '17 at 4:07



















          12














          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





          share|improve this answer
































            6














            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);






            share|improve this answer




















            • 1





              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?

              – jpaugh
              Aug 31 '16 at 21:26






            • 1





              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

              – Eske Rahn
              Sep 9 '16 at 12:50











            • This is exactly what I needed! Thank you.

              – Nitin Badole
              Jan 18 at 10:30


















            3














            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





            share|improve this answer























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

              – Aaron Schultz
              Oct 16 '17 at 20:06


















            1















            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





            share|improve this answer




















            • 1





              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.

              – Stewart
              May 1 '17 at 9:30











            • 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(''))

              – Shnugo
              Jul 19 '18 at 9:02


















            1














            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)-@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<=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





            share|improve this answer























            • 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.

              – Sean Lange
              Mar 12 at 18:51


















            1














            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






            share|improve this answer
































              1














              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!






              share|improve this answer






























                0














                ;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...






                share|improve this answer




















                • 6





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

                  – Leigh
                  Jul 28 '12 at 20:50











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

                  – TamusJRoyce
                  Jan 5 '15 at 16:24







                • 1





                  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.

                  – Oedhel Setren
                  Feb 27 '15 at 15:20











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

                  – Eske Rahn
                  Apr 7 '16 at 22:38


















                0














                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.






                share|improve this answer
































                  0














                  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





                  share|improve this answer























                  • 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.

                    – Sean Lange
                    Mar 12 at 18:51









                  protected by Pரதீப் Mar 30 '16 at 10:01



                  Thank you for your interest in this question.
                  Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



                  Would you like to answer one of these unanswered questions instead?














                  14 Answers
                  14






                  active

                  oldest

                  votes








                  14 Answers
                  14






                  active

                  oldest

                  votes









                  active

                  oldest

                  votes






                  active

                  oldest

                  votes









                  225














                  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





                  share|improve this answer

























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

                    – Aliostad
                    Mar 30 '11 at 23:20






                  • 1





                    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))?

                    – ca9163d9
                    Feb 21 '12 at 23:58







                  • 4





                    @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...

                    – RichardTheKiwi
                    Feb 22 '12 at 8:35






                  • 3





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

                    – RichardTheKiwi
                    Jan 14 '14 at 20:50






                  • 8





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

                    – smoore4
                    Jul 15 '16 at 15:27















                  225














                  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





                  share|improve this answer

























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

                    – Aliostad
                    Mar 30 '11 at 23:20






                  • 1





                    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))?

                    – ca9163d9
                    Feb 21 '12 at 23:58







                  • 4





                    @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...

                    – RichardTheKiwi
                    Feb 22 '12 at 8:35






                  • 3





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

                    – RichardTheKiwi
                    Jan 14 '14 at 20:50






                  • 8





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

                    – smoore4
                    Jul 15 '16 at 15:27













                  225












                  225








                  225







                  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





                  share|improve this 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 > ''
                  )

                  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






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Apr 24 '18 at 20:50









                  LuizLoyola

                  14314




                  14314










                  answered Mar 30 '11 at 23:18









                  RichardTheKiwiRichardTheKiwi

                  87.6k22158230




                  87.6k22158230












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

                    – Aliostad
                    Mar 30 '11 at 23:20






                  • 1





                    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))?

                    – ca9163d9
                    Feb 21 '12 at 23:58







                  • 4





                    @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...

                    – RichardTheKiwi
                    Feb 22 '12 at 8:35






                  • 3





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

                    – RichardTheKiwi
                    Jan 14 '14 at 20:50






                  • 8





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

                    – smoore4
                    Jul 15 '16 at 15:27

















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

                    – Aliostad
                    Mar 30 '11 at 23:20






                  • 1





                    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))?

                    – ca9163d9
                    Feb 21 '12 at 23:58







                  • 4





                    @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...

                    – RichardTheKiwi
                    Feb 22 '12 at 8:35






                  • 3





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

                    – RichardTheKiwi
                    Jan 14 '14 at 20:50






                  • 8





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

                    – smoore4
                    Jul 15 '16 at 15:27
















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

                  – Aliostad
                  Mar 30 '11 at 23:20





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

                  – Aliostad
                  Mar 30 '11 at 23:20




                  1




                  1





                  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))?

                  – ca9163d9
                  Feb 21 '12 at 23:58






                  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))?

                  – ca9163d9
                  Feb 21 '12 at 23:58





                  4




                  4





                  @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...

                  – RichardTheKiwi
                  Feb 22 '12 at 8:35





                  @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...

                  – RichardTheKiwi
                  Feb 22 '12 at 8:35




                  3




                  3





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

                  – RichardTheKiwi
                  Jan 14 '14 at 20:50





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

                  – RichardTheKiwi
                  Jan 14 '14 at 20:50




                  8




                  8





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

                  – smoore4
                  Jul 15 '16 at 15:27





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

                  – smoore4
                  Jul 15 '16 at 15:27













                  102














                  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






                  share|improve this answer




















                  • 7





                    very important answer

                    – Syed Md. Kamruzzaman
                    Mar 6 '17 at 6:21











                  • 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.

                    – Stewart
                    Apr 28 '17 at 16:12















                  102














                  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






                  share|improve this answer




















                  • 7





                    very important answer

                    – Syed Md. Kamruzzaman
                    Mar 6 '17 at 6:21











                  • 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.

                    – Stewart
                    Apr 28 '17 at 16:12













                  102












                  102








                  102







                  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






                  share|improve this 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







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Aug 1 '17 at 16:26

























                  answered Mar 30 '16 at 10:01









                  Pரதீப்Pரதீப்

                  76k1282115




                  76k1282115







                  • 7





                    very important answer

                    – Syed Md. Kamruzzaman
                    Mar 6 '17 at 6:21











                  • 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.

                    – Stewart
                    Apr 28 '17 at 16:12












                  • 7





                    very important answer

                    – Syed Md. Kamruzzaman
                    Mar 6 '17 at 6:21











                  • 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.

                    – Stewart
                    Apr 28 '17 at 16:12







                  7




                  7





                  very important answer

                  – Syed Md. Kamruzzaman
                  Mar 6 '17 at 6:21





                  very important answer

                  – Syed Md. Kamruzzaman
                  Mar 6 '17 at 6:21













                  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.

                  – Stewart
                  Apr 28 '17 at 16:12





                  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.

                  – Stewart
                  Apr 28 '17 at 16:12











                  79














                  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);





                  share|improve this answer


















                  • 8





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

                    – user1151923
                    Mar 4 '15 at 18:22











                  • 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?

                    – Control
                    Jul 8 '15 at 16:46











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

                    – chazbot7
                    Jan 6 '17 at 0:46











                  • this worked perfectly for sql 2014. thanks!

                    – Chris Brickhouse
                    Apr 8 '18 at 19:20















                  79














                  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);





                  share|improve this answer


















                  • 8





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

                    – user1151923
                    Mar 4 '15 at 18:22











                  • 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?

                    – Control
                    Jul 8 '15 at 16:46











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

                    – chazbot7
                    Jan 6 '17 at 0:46











                  • this worked perfectly for sql 2014. thanks!

                    – Chris Brickhouse
                    Apr 8 '18 at 19:20













                  79












                  79








                  79







                  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);





                  share|improve this answer













                  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);






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Apr 18 '13 at 12:33









                  bvrbvr

                  4,0661421




                  4,0661421







                  • 8





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

                    – user1151923
                    Mar 4 '15 at 18:22











                  • 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?

                    – Control
                    Jul 8 '15 at 16:46











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

                    – chazbot7
                    Jan 6 '17 at 0:46











                  • this worked perfectly for sql 2014. thanks!

                    – Chris Brickhouse
                    Apr 8 '18 at 19:20












                  • 8





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

                    – user1151923
                    Mar 4 '15 at 18:22











                  • 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?

                    – Control
                    Jul 8 '15 at 16:46











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

                    – chazbot7
                    Jan 6 '17 at 0:46











                  • this worked perfectly for sql 2014. thanks!

                    – Chris Brickhouse
                    Apr 8 '18 at 19:20







                  8




                  8





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

                  – user1151923
                  Mar 4 '15 at 18:22





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

                  – user1151923
                  Mar 4 '15 at 18:22













                  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?

                  – Control
                  Jul 8 '15 at 16:46





                  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?

                  – Control
                  Jul 8 '15 at 16:46













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

                  – chazbot7
                  Jan 6 '17 at 0:46





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

                  – chazbot7
                  Jan 6 '17 at 0:46













                  this worked perfectly for sql 2014. thanks!

                  – Chris Brickhouse
                  Apr 8 '18 at 19:20





                  this worked perfectly for sql 2014. thanks!

                  – Chris Brickhouse
                  Apr 8 '18 at 19:20











                  16














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





                  share|improve this answer




















                  • 3





                    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!

                    – tobriand
                    Nov 3 '15 at 13:45











                  • 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 ?

                    – Jayendran
                    Jun 7 '17 at 15:29







                  • 1





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

                    – Akbar Kautsar
                    Aug 11 '17 at 4:07
















                  16














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





                  share|improve this answer




















                  • 3





                    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!

                    – tobriand
                    Nov 3 '15 at 13:45











                  • 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 ?

                    – Jayendran
                    Jun 7 '17 at 15:29







                  • 1





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

                    – Akbar Kautsar
                    Aug 11 '17 at 4:07














                  16












                  16








                  16







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





                  share|improve this answer















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






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Jan 28 '14 at 15:47









                  iCodez

                  110k22215221




                  110k22215221










                  answered Jan 28 '14 at 15:29









                  user3245135user3245135

                  18513




                  18513







                  • 3





                    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!

                    – tobriand
                    Nov 3 '15 at 13:45











                  • 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 ?

                    – Jayendran
                    Jun 7 '17 at 15:29







                  • 1





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

                    – Akbar Kautsar
                    Aug 11 '17 at 4:07













                  • 3





                    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!

                    – tobriand
                    Nov 3 '15 at 13:45











                  • 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 ?

                    – Jayendran
                    Jun 7 '17 at 15:29







                  • 1





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

                    – Akbar Kautsar
                    Aug 11 '17 at 4:07








                  3




                  3





                  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!

                  – tobriand
                  Nov 3 '15 at 13:45





                  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!

                  – tobriand
                  Nov 3 '15 at 13:45













                  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 ?

                  – Jayendran
                  Jun 7 '17 at 15:29






                  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 ?

                  – Jayendran
                  Jun 7 '17 at 15:29





                  1




                  1





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

                  – Akbar Kautsar
                  Aug 11 '17 at 4:07






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

                  – Akbar Kautsar
                  Aug 11 '17 at 4:07












                  12














                  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





                  share|improve this answer





























                    12














                    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





                    share|improve this answer



























                      12












                      12








                      12







                      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





                      share|improve this 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






                      share|improve this answer














                      share|improve this answer



                      share|improve this answer








                      edited Aug 1 '17 at 7:07

























                      answered Jan 14 '14 at 6:20









                      dszdsz

                      2,6132122




                      2,6132122





















                          6














                          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);






                          share|improve this answer




















                          • 1





                            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?

                            – jpaugh
                            Aug 31 '16 at 21:26






                          • 1





                            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

                            – Eske Rahn
                            Sep 9 '16 at 12:50











                          • This is exactly what I needed! Thank you.

                            – Nitin Badole
                            Jan 18 at 10:30















                          6














                          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);






                          share|improve this answer




















                          • 1





                            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?

                            – jpaugh
                            Aug 31 '16 at 21:26






                          • 1





                            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

                            – Eske Rahn
                            Sep 9 '16 at 12:50











                          • This is exactly what I needed! Thank you.

                            – Nitin Badole
                            Jan 18 at 10:30













                          6












                          6








                          6







                          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);






                          share|improve this 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);







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Apr 6 '17 at 1:03

























                          answered Apr 2 '16 at 1:57









                          Eske RahnEske Rahn

                          64078




                          64078







                          • 1





                            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?

                            – jpaugh
                            Aug 31 '16 at 21:26






                          • 1





                            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

                            – Eske Rahn
                            Sep 9 '16 at 12:50











                          • This is exactly what I needed! Thank you.

                            – Nitin Badole
                            Jan 18 at 10:30












                          • 1





                            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?

                            – jpaugh
                            Aug 31 '16 at 21:26






                          • 1





                            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

                            – Eske Rahn
                            Sep 9 '16 at 12:50











                          • This is exactly what I needed! Thank you.

                            – Nitin Badole
                            Jan 18 at 10:30







                          1




                          1





                          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?

                          – jpaugh
                          Aug 31 '16 at 21:26





                          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?

                          – jpaugh
                          Aug 31 '16 at 21:26




                          1




                          1





                          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

                          – Eske Rahn
                          Sep 9 '16 at 12:50





                          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

                          – Eske Rahn
                          Sep 9 '16 at 12:50













                          This is exactly what I needed! Thank you.

                          – Nitin Badole
                          Jan 18 at 10:30





                          This is exactly what I needed! Thank you.

                          – Nitin Badole
                          Jan 18 at 10:30











                          3














                          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





                          share|improve this answer























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

                            – Aaron Schultz
                            Oct 16 '17 at 20:06















                          3














                          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





                          share|improve this answer























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

                            – Aaron Schultz
                            Oct 16 '17 at 20:06













                          3












                          3








                          3







                          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





                          share|improve this 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 > 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






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Nov 7 '13 at 13:22









                          JayveeJayvee

                          6,33121732




                          6,33121732












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

                            – Aaron Schultz
                            Oct 16 '17 at 20:06

















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

                            – Aaron Schultz
                            Oct 16 '17 at 20:06
















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

                          – Aaron Schultz
                          Oct 16 '17 at 20:06





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

                          – Aaron Schultz
                          Oct 16 '17 at 20:06











                          1















                          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





                          share|improve this answer




















                          • 1





                            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.

                            – Stewart
                            May 1 '17 at 9:30











                          • 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(''))

                            – Shnugo
                            Jul 19 '18 at 9:02















                          1















                          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





                          share|improve this answer




















                          • 1





                            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.

                            – Stewart
                            May 1 '17 at 9:30











                          • 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(''))

                            – Shnugo
                            Jul 19 '18 at 9:02













                          1












                          1








                          1








                          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





                          share|improve this 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;')
                          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






                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Mar 16 '15 at 16:48

























                          answered Mar 16 '15 at 16:40









                          tommyluxtommylux

                          715




                          715







                          • 1





                            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.

                            – Stewart
                            May 1 '17 at 9:30











                          • 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(''))

                            – Shnugo
                            Jul 19 '18 at 9:02












                          • 1





                            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.

                            – Stewart
                            May 1 '17 at 9:30











                          • 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(''))

                            – Shnugo
                            Jul 19 '18 at 9:02







                          1




                          1





                          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.

                          – Stewart
                          May 1 '17 at 9:30





                          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.

                          – Stewart
                          May 1 '17 at 9:30













                          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(''))

                          – Shnugo
                          Jul 19 '18 at 9:02





                          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(''))

                          – Shnugo
                          Jul 19 '18 at 9:02











                          1














                          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)-@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<=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





                          share|improve this answer























                          • 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.

                            – Sean Lange
                            Mar 12 at 18:51















                          1














                          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)-@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<=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





                          share|improve this answer























                          • 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.

                            – Sean Lange
                            Mar 12 at 18:51













                          1












                          1








                          1







                          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)-@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<=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





                          share|improve this 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<=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<=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






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Jan 31 '18 at 13:22









                          mr Rmr R

                          325414




                          325414












                          • 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.

                            – Sean Lange
                            Mar 12 at 18:51

















                          • 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.

                            – Sean Lange
                            Mar 12 at 18:51
















                          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.

                          – Sean Lange
                          Mar 12 at 18:51





                          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.

                          – Sean Lange
                          Mar 12 at 18:51











                          1














                          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






                          share|improve this answer





























                            1














                            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






                            share|improve this answer



























                              1












                              1








                              1







                              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






                              share|improve this 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







                              share|improve this answer














                              share|improve this answer



                              share|improve this answer








                              edited Oct 15 '18 at 15:42









                              MEdwin

                              1,168216




                              1,168216










                              answered Aug 22 '18 at 2:56









                              Jag KandasamyJag Kandasamy

                              413




                              413





















                                  1














                                  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!






                                  share|improve this answer



























                                    1














                                    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!






                                    share|improve this answer

























                                      1












                                      1








                                      1







                                      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!






                                      share|improve this 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!







                                      share|improve this answer












                                      share|improve this answer



                                      share|improve this answer










                                      answered Jan 7 at 6:27









                                      DungeonDungeon

                                      246113




                                      246113





















                                          0














                                          ;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...






                                          share|improve this answer




















                                          • 6





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

                                            – Leigh
                                            Jul 28 '12 at 20:50











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

                                            – TamusJRoyce
                                            Jan 5 '15 at 16:24







                                          • 1





                                            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.

                                            – Oedhel Setren
                                            Feb 27 '15 at 15:20











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

                                            – Eske Rahn
                                            Apr 7 '16 at 22:38















                                          0














                                          ;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...






                                          share|improve this answer




















                                          • 6





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

                                            – Leigh
                                            Jul 28 '12 at 20:50











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

                                            – TamusJRoyce
                                            Jan 5 '15 at 16:24







                                          • 1





                                            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.

                                            – Oedhel Setren
                                            Feb 27 '15 at 15:20











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

                                            – Eske Rahn
                                            Apr 7 '16 at 22:38













                                          0












                                          0








                                          0







                                          ;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...






                                          share|improve this 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 > ''
                                          )
                                          SELECT SomeID, OtherID, Data
                                          FROM tmp
                                          ORDER BY SomeID


                                          with only tiny little modification to above query...







                                          share|improve this answer














                                          share|improve this answer



                                          share|improve this answer








                                          edited Jul 28 '12 at 17:50









                                          Tisho

                                          6,44653349




                                          6,44653349










                                          answered Jul 28 '12 at 12:58









                                          Klix MediaKlix Media

                                          251




                                          251







                                          • 6





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

                                            – Leigh
                                            Jul 28 '12 at 20:50











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

                                            – TamusJRoyce
                                            Jan 5 '15 at 16:24







                                          • 1





                                            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.

                                            – Oedhel Setren
                                            Feb 27 '15 at 15:20











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

                                            – Eske Rahn
                                            Apr 7 '16 at 22:38












                                          • 6





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

                                            – Leigh
                                            Jul 28 '12 at 20:50











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

                                            – TamusJRoyce
                                            Jan 5 '15 at 16:24







                                          • 1





                                            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.

                                            – Oedhel Setren
                                            Feb 27 '15 at 15:20











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

                                            – Eske Rahn
                                            Apr 7 '16 at 22:38







                                          6




                                          6





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

                                          – Leigh
                                          Jul 28 '12 at 20:50





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

                                          – Leigh
                                          Jul 28 '12 at 20:50













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

                                          – TamusJRoyce
                                          Jan 5 '15 at 16:24






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

                                          – TamusJRoyce
                                          Jan 5 '15 at 16:24





                                          1




                                          1





                                          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.

                                          – Oedhel Setren
                                          Feb 27 '15 at 15:20





                                          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.

                                          – Oedhel Setren
                                          Feb 27 '15 at 15:20













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

                                          – Eske Rahn
                                          Apr 7 '16 at 22:38





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

                                          – Eske Rahn
                                          Apr 7 '16 at 22:38











                                          0














                                          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.






                                          share|improve this answer





























                                            0














                                            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.






                                            share|improve this answer



























                                              0












                                              0








                                              0







                                              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.






                                              share|improve this 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.







                                              share|improve this answer














                                              share|improve this answer



                                              share|improve this answer








                                              edited Oct 6 '17 at 10:26

























                                              answered Oct 6 '17 at 10:21









                                              Arun Pratap SinghArun Pratap Singh

                                              2,2161816




                                              2,2161816





















                                                  0














                                                  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





                                                  share|improve this answer























                                                  • 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.

                                                    – Sean Lange
                                                    Mar 12 at 18:51















                                                  0














                                                  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





                                                  share|improve this answer























                                                  • 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.

                                                    – Sean Lange
                                                    Mar 12 at 18:51













                                                  0












                                                  0








                                                  0







                                                  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





                                                  share|improve this 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>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






                                                  share|improve this answer












                                                  share|improve this answer



                                                  share|improve this answer










                                                  answered Feb 13 at 7:18









                                                  SpiderSpider

                                                  344616




                                                  344616












                                                  • 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.

                                                    – Sean Lange
                                                    Mar 12 at 18:51

















                                                  • 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.

                                                    – Sean Lange
                                                    Mar 12 at 18:51
















                                                  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.

                                                  – Sean Lange
                                                  Mar 12 at 18:51





                                                  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.

                                                  – Sean Lange
                                                  Mar 12 at 18:51





                                                  protected by Pரதீப் Mar 30 '16 at 10:01



                                                  Thank you for your interest in this question.
                                                  Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



                                                  Would you like to answer one of these unanswered questions instead?



                                                  Popular posts from this blog

                                                  Identity Server 4 is not redirecting to Angular app after login2019 Community Moderator ElectionIdentity Server 4 and dockerIdentityserver implicit flow unauthorized_clientIdentityServer Hybrid Flow - Access Token is null after user successful loginIdentity Server to MVC client : Page Redirect After loginLogin with Steam OpenId(oidc-client-js)Identity Server 4+.NET Core 2.0 + IdentityIdentityServer4 post-login redirect not working in Edge browserCall to IdentityServer4 generates System.NullReferenceException: Object reference not set to an instance of an objectIdentityServer4 without HTTPS not workingHow to get Authorization code from identity server without login form

                                                  2005 Ahvaz unrest Contents Background Causes Casualties Aftermath See also References Navigation menue"At Least 10 Are Killed by Bombs in Iran""Iran"Archived"Arab-Iranians in Iran to make April 15 'Day of Fury'"State of Mind, State of Order: Reactions to Ethnic Unrest in the Islamic Republic of Iran.10.1111/j.1754-9469.2008.00028.x"Iran hangs Arab separatists"Iran Overview from ArchivedConstitution of the Islamic Republic of Iran"Tehran puzzled by forged 'riots' letter""Iran and its minorities: Down in the second class""Iran: Handling Of Ahvaz Unrest Could End With Televised Confessions""Bombings Rock Iran Ahead of Election""Five die in Iran ethnic clashes""Iran: Need for restraint as anniversary of unrest in Khuzestan approaches"Archived"Iranian Sunni protesters killed in clashes with security forces"Archived

                                                  Can't initialize raids on a new ASUS Prime B360M-A motherboard2019 Community Moderator ElectionSimilar to RAID config yet more like mirroring solution?Can't get motherboard serial numberWhy does the BIOS entry point start with a WBINVD instruction?UEFI performance Asus Maximus V Extreme