CODEWITHSUNDEEP
sqlsql-serverstringsplit
bootica
bootica

Reputation: 771

SQL Server : split multiple strings into one row each

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

Upvotes: 3

Views: 1075

Answers (2)

gofr1
gofr1

Reputation: 15977

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)

Upvotes: 4

user6448704
user6448704

Reputation:

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

Upvotes: 1

Related Questions