Splitting Strings with Regular Terms to Columns

Question

I'm having trouble with the following query. I need to split a string values into new columns based of LineFeeds.

The data is presented as

|NUMBER   |Answer
|RN110455 |very satisfied very satisfied very satisfied very satisfied very satisfied |

I came across the following section of code however i keep getting the following error message

Msg 240, Level 16, State 1, Line 1 Types don't match between the anchor and the recursive part in column "SplitValue" of recursive query "SplitValues".

I'm hoping someone can tell me where i'm going wrong because I cant figure it out. I believe that all the types matched up.

;WITH SplitValues (ID, OriginalValue, SplitValue, Level)
AS
(
    SELECT  i.Number, i.ANSWER, CAST('' AS VARCHAR(MAX)), 0 FROM Survey i

    UNION ALL

    SELECT  ID
    ,   SUBSTRING(OriginalValue, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) + 2 END, LEN(OriginalValue))
    ,   SUBSTRING(OriginalValue, 0, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) END)
    ,   Level + 1
    FROM    SplitValues
    WHERE   LEN(SplitValues.OriginalValue) > 0
)

SELECT  ID, [1] AS Level1, [2] AS Level2, [3] AS Level3, [4] AS Level4, [5] AS Level5
FROM    (
    SELECT  ID, Level, SplitValue
    FROM    SplitValues
    WHERE   Level > 0
    ) AS p
PIVOT   (MAX(SplitValue) FOR Level IN ([1], [2], [3], [4], [5])) AS pvt

The above code seemed like the most fit for purpose solution however i'm just not getting there.

I am running. Microsoft SQL Server 2014

Answer 1

Try this. You have to cast i.Answer too.

;WITH SplitValues (ID, OriginalValue, SplitValue, Level)
AS
(
    SELECT  'RN110455', cast('very satisfied very satisfied very satisfied very satisfied very satisfied' as varchar(max))
    , CAST('' AS VARCHAR(MAX)), 0 

    UNION ALL

    SELECT  ID
    ,   SUBSTRING(OriginalValue, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) + 2 END, LEN(OriginalValue))
    ,   SUBSTRING(OriginalValue, 0, CASE WHEN CHARINDEX(CHAR(13), OriginalValue) = 0 THEN LEN(OriginalValue) + 1 ELSE CHARINDEX(CHAR(13), OriginalValue) END)
    ,   Level + 1
    FROM    SplitValues
    WHERE   LEN(SplitValues.OriginalValue) > 0
)

SELECT  ID, [1] AS Level1, [2] AS Level2, [3] AS Level3, [4] AS Level4, [5] AS Level5
FROM    (
    SELECT  ID, Level, SplitValue
    FROM    SplitValues
    WHERE   Level > 0
    ) AS p
PIVOT   (MAX(SplitValue) FOR Level IN ([1], [2], [3], [4], [5])) AS pvt