Merenix Merenix - 3 months ago 15
SQL Question

Splitting Strings with Regular Terms to Columns

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 SURVEYRESULTM1 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

Thanks kindly .


EDIT: I Forgot to advise what system i am running. Microsoft SQL
Server 2014,
Also this is my first question so if it sucks sorry

Answer

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
Comments