SQL Server : split multiple strings into one row each

Question

I´m new to SQL Server and I'm dealing with this following problem.

Let's say I have a column that looks like that:

ID  String
-------------------------
1   Today is a good day!
2   Whatever
3   Hello my friend

So my goal was to split these sentences into this:

ID  String1   String2    String3    String4    String5
------------------------------------------------------
1   Today     is         a          good       day!
2   Whatever
3   Hello     my         friend

I tried using this code:

CREATE FUNCTION [dbo].[SplitString] 
     (@str nvarchar(max), 
      @separator char(1))
RETURNS TABLE
AS
    RETURN (
         WITH tokens(p, a, b) AS 
         (
             SELECT
                 CAST(1 AS BIGINT), 
                 CAST(1 AS BIGINT), 
                 CHARINDEX(@separator, @str)

             UNION ALL

             SELECT
                 p + 1, 
                 b + 1, 
                 CHARINDEX(@separator, @str, b + 1)
             FROM 
                 tokens
             WHERE
                 b > 0
         )
         SELECT
             --p-1 ItemIndex,
             SUBSTRING(@str, a, 
                           CASE WHEN b > 0 THEN b-a 
                                ELSE LEN(@str) 
                           END) AS Item
         FROM tokens)
GO

which I found here at Stackoverflow.

It seems to work, for single strings, but its not working for multiple strings. And it puts every word in a new row like this:

Item
Today
is
a
good
day!

So how do I adjust the code, so it does the desired?

One more problem is, that I don´t really know the # of words in each string.

So it could differ, e.g. from 1 word to 100 words.

I would be very happy if someone could help me with this problem, as I´m only starting to learn how to use SQL.

Thanks! MG

looks like a homework at scool. if there is no limitation for column numbers you have to use dynamic query. — xdd
– xdd, Commented Jun 10, 2016 at 13:47
Here are some better performing splitter options. sqlperformance.com/2012/07/t-sql-queries/split-strings To get this back into columns you will need to use dynamic cross tab or a dynamic pivot. — Sean Lange
– Sean Lange, Commented Jun 10, 2016 at 13:56
For somebody just learning t-sql this is a pretty advanced problem you are working on. — Sean Lange
– Sean Lange, Commented Jun 10, 2016 at 13:57

gofr1 · Accepted Answer · 2016-06-14 06:37:19Z

4

With the help of XML:

DECLARE @xml xml

;WITH cte AS (
SELECT *
FROM (VALUES
(1, 'Today is a good day!'),
(2, 'Whatever'),
(3, 'Hello my friend')
) as t(ID, String)
)

SELECT @xml = (
SELECT CAST('<i id="' + CAST(ID as nvarchar(10)) + '"><w>' + REPLACE(REPLACE(String,' ','</w><w>'),'&','&amp;') + '</w></i>' as xml)
FROM cte
FOR XML PATH('')
)

SELECT  t.v.value('@id','int') as ID,
        t.v.value('w[1]','nvarchar(10)') as String1,
        t.v.value('w[2]','nvarchar(10)') as String2,
        t.v.value('w[3]','nvarchar(10)') as String3,
        t.v.value('w[4]','nvarchar(10)') as String4,
        t.v.value('w[5]','nvarchar(10)') as String5,
        t.v.value('w[6]','nvarchar(10)') as String6
FROM @xml.nodes('/i') as t(v)

Output:

ID          String1    String2    String3    String4    String5    String6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           Today      is         a          good       day!       NULL
2           Whatever   NULL       NULL       NULL       NULL       NULL
3           Hello      my         friend     NULL       NULL       NULL

EDIT

To use with actual table:

DECLARE @xml xml

SELECT @xml = (
SELECT CAST('<i id="' + CAST(ID as nvarchar(10)) + '"><w>' + REPLACE(big_string,' ','</w><w>') + '</w></i>' as xml)
FROM [table]
FOR XML PATH('')
)

SELECT  t.v.value('@id','int') as ID,
        t.v.value('w[1]','nvarchar(10)') as String1,
        t.v.value('w[2]','nvarchar(10)') as String2,
        t.v.value('w[3]','nvarchar(10)') as String3,
        t.v.value('w[4]','nvarchar(10)') as String4,
        t.v.value('w[5]','nvarchar(10)') as String5,
        t.v.value('w[6]','nvarchar(10)') as String6,
        t.v.value('w[7]','nvarchar(10)') as String7
FROM @xml.nodes('/i') as t(v)

edited Jun 14, 2016 at 6:37

answered Jun 10, 2016 at 14:03

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

bootica Over a year ago

So this is the Code I was looking for. One short question: Does ist work with Strings bigger than 6 words? Does it drop the words [7:]? It would be okay like that.

bootica Over a year ago

Okay! So what I tried to do now is to adjust the code to my actual needs. I have a Table 'table' and I only want one column 'big_string' to be part of the code & the output. I tried it myself but I failed to adjust it like that.

gofr1 Over a year ago

I add query to my answer, please, check.

bootica Over a year ago

I´m getting this error message: "Msg 9411, Level 16, State 1, Line 3 XML parsing: line 1, character 41, semicolon expected". If I double click on the error message he selects "SELECT @xml = (" this line. Plus do I really need the ID, part? I know my original example was like that but basically I just have one relevant column.

gofr1 Over a year ago

Without ID you cannot output result as rows, it will be just one big big row with many columns. If you have just one column you can use this ROW_NUMBER() OVER(ORDER BY big_string) instead of ID.

|

score 1 · Accepted Answer · 2016-06-10 17:16:20Z

I edited your code. Try to run this. Columns are created on the fly depending on the rows to be append. The result is like this: return rows It is not in order. Try to edit the upper code to include your Id so that it will be in order again.

DECLARE @TBL TABLE (Id int, Description varchar(max))
CREATE table #tblResult(Description varchar(max))

INSERT INTO @TBL
SELECT 1, 'Today is a good day!'

DECLARE @separator varchar(1) = ' ', @str varchar(max)
SELECT @str = Description FROM @TBL


     ;WITH tokens(p, a, b) AS 
     (
         SELECT
             CAST(1 AS BIGINT), 
             CAST(1 AS BIGINT), 
             CHARINDEX(@separator, @str)

         UNION ALL

         SELECT
             p + 1, 
             b + 1, 
             CHARINDEX(@separator, @str, b + 1)
         FROM 
             tokens
         WHERE
             b > 0
     )

     INSERT INTO #tblResult
     SELECT
         SUBSTRING(@str, a, 
                       CASE WHEN b > 0 THEN b-a 
                            ELSE LEN(@str) 
                       END) AS Item
                       FROM tokens

    DECLARE @x nvarchar(MAX), @query nvarchar(MAX)

    select @x = STUFF((SELECT ',' + QUOTENAME(Description) 
                from #tblResult
                group by Description
                order by Description
        FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(MAX)') 
    ,1,1,'')


    set @query = N'SELECT ' + @x + N' from 
         (
            select Description
            from #tblResult
        ) x
        pivot 
        (
            max(Description)
            for Description in (' + @x + N')
        ) p '

  exec sp_executesql @query;


  drop table #tblResult

This code seems to work as well but the output is not in order!?

Collectives™ on Stack Overflow

SQL Server : split multiple strings into one row each

2 Answers 2

14 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related