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, Jun 10 '16 at 13:47
Here are some better performing splitter options. http://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, Jun 10 '16 at 13:56
For somebody just learning t-sql this is a pretty advanced problem you are working on. — Sean Lange, Jun 10 '16 at 13:57

gofr1 · Accepted Answer · 2016-06-14T06:37:19.973

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 '16 at 06:37

answered Jun 10 '16 at 14:03

gofr1

15,741
11
42
52

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. – mgruber Jun 13 '16 at 06:53
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. – mgruber Jun 13 '16 at 07:56
1

I add query to my answer, please, check. – gofr1 Jun 13 '16 at 08:07
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. – mgruber Jun 13 '16 at 11:30
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. – gofr1 Jun 13 '16 at 11:52
What is a value of big_string in a very first row? – gofr1 Jun 13 '16 at 11:54
I asked about content in very first row. If you make simple select what will be in the first row. – gofr1 Jun 13 '16 at 13:51
No, man :) I ask about content, I guess there is some special characters in a content. – gofr1 Jun 13 '16 at 14:17
I guess so. With example from your answer all works fine and I have used same method for splitting into columns many times. Maybe you give an actual example of data from your table? If it is sensual data replace words with some dummy text. – gofr1 Jun 13 '16 at 15:29
I think I probably detected the problem. The column contains company names and they sometimes contain an &. E.g. AT&T. I read that an & will lead to this problem!? Is it possible? – mgruber Jun 14 '16 at 06:24
You can add one more replace - this '&' to this '&' that is why you got 'expecting semicolon' error – gofr1 Jun 14 '16 at 06:31
I add this replacement to second query – gofr1 Jun 14 '16 at 06:37
No, if you have some ID field. – gofr1 Jun 14 '16 at 06:50
You can use ROW_NUMBER() OVER (ORDER BY (SELECT 1)) It will simulate ID. If my answer helps you be free to upvote/accept it :) – gofr1 Jun 14 '16 at 18:17

score 1 · Answer 2 · 2016-06-10T17:16:20.627

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!? — mgruber, Jun 13 '16 at 06:59

SQL Server : split multiple strings into one row each

2 Answers2

Linked