将具有正则项的字符串拆分为列 [英] Splitting Strings with Regular Terms to Columns

问题描述

我在执行以下查询时遇到问题.我需要根据 LineFeeds 将字符串值拆分为新列.

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

数据显示为

|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 类型不匹配锚点和递归的SplitValue"列中的递归部分查询SplitValues".

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.

我在跑步.微软 SQL Server 2014

I am running. Microsoft SQL Server 2014

推荐答案

试试这个.你也必须投 i.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

这篇关于将具有正则项的字符串拆分为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！