T-SQL：使用多个分隔符解析字符串

Question

我需要能够查询SharePoint数据库以获取调查结果。我遇到问题的数据类型是“评分刻度”值。因此，每个表列中的数据表示一整组子问题及其答案。

以下是一个列中包含的示例内容：

1. 我们的功能已经定义了如何测量生产中的硬件/软件可用性;#3#2. 在我们的功能中存在可用性阈值水平（例如，SLA）;#3#3. 当有阈值违规时，我们的功能会遵循定义好的流程;#4#4. 我们的功能收集并维护可用性数据;#4#5. 比较分析有助于识别可用性数据的趋势;#4#6. 运营级别协议（OLA）指导我们与其他内部团队的互动;#4#

问题以分号结束，它们的答案在两个#符号内。因此，第一个问题的答案是3。

当我导出调查结果时，它将每个问题格式化为列标题，答案作为单元格下方的值，这样可以获得每个问题的平均值，并且希望能够从SQL查询中复制该值。

但如果我能够将查询结果放入两列（问题、答案）……我会非常高兴。

感谢您的帮助。

非常感谢

Hank Stallings

*****补充说明：

DECLARE @Table TABLE(  
        QuestionSource VARCHAR(50),  
        QA VARCHAR(5000)  
) 

DECLARE @ReturnTable TABLE( 
        QuestionSource VARCHAR(50), 
        Question VARCHAR(5000),  
        Answer int  
) 

DECLARE @XmlField XML, 
        @QuestionSource VARCHAR(50) 

INSERT INTO @Table SELECT 
'Availability' AS QuestionSource,CONVERT(varchar(5000),ntext1) FROM UserData WHERE tp_ContentType = 'My Survey' 
INSERT INTO @Table SELECT 
'Capacity' AS QuestionSource,CONVERT(varchar(5000),ntext2) FROM UserData WHERE tp_ContentType = 'My Survey' 

--SELECT * FROM @Table 

DECLARE Cur CURSOR FOR  
SELECT  QuestionSource, 
        CAST(Val AS XML) XmlVal  
FROM    (  
            SELECT  QuestionSource, 
            LEFT(Vals, LEN(Vals) - LEN('<option><q>')) Val  
            FROM    (  
                        SELECT  QuestionSource, 
                            '<option><q>' + REPLACE(REPLACE(REPLACE(QA,'&','&amp;'), ';#','</q><a>'), '#', '</a></option><option><q>') Vals  
                        FROM @Table 

                    ) sub  
        ) sub  

OPEN Cur  
FETCH NEXT FROM Cur INTO @QuestionSource,@XmlField  

WHILE @@FETCH_STATUS = 0   
BEGIN  
    INSERT INTO @ReturnTable  
    SELECT  @QuestionSource, 
            T.split.query('q').value('.', 'nvarchar(max)') question,  
            T.split.query('a').value('.', 'nvarchar(max)') answer  
    FROM    @XmlField.nodes('/option') T(split)  
    FETCH NEXT FROM Cur INTO @QuestionSource,@XmlField  
END  

CLOSE Cur  
DEALLOCATE Cur  

SELECT * FROM @ReturnTable 

- Hank Stallings

---

我宁愿戳瞎自己的眼睛也不使用那个光标！我会像我的答案一样采用基于集合的方法。 - KM.

Answer 1

您需要设置一个分割函数，但一旦设置完成，请尝试使用此无游标解决方案：

我更喜欢在TSQL中使用数字表方法来拆分字符串

为使此方法有效，您需要进行一次表设置：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

一旦设置了 Numbers 表，创建此拆分函数，它将返回空字符串和行号：

CREATE FUNCTION [dbo].[FN_ListToTableRows]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this WILL return empty rows
    ----------------
    SELECT
        ROW_NUMBER() OVER(ORDER BY number) AS RowNumber
            ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue
        FROM (
                 SELECT @SplitOn + @List + @SplitOn AS ListValue
             ) AS InnerQuery
            INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
        WHERE SUBSTRING(ListValue, number, 1) = @SplitOn
);
GO 

现在你可以轻松地将CSV字符串拆分成表格并进行连接，注意此拆分函数返回空字符串和行号：

select * from dbo.FN_ListToTableRows(',','1,2,3,,,4,5,6777,,,')

输出：

RowNumber            ListValue
-------------------- ------------
1                    1
2                    2
3                    3
4                    
5                    
6                    4
7                    5
8                    6777
9                    
10                   
11                   

(11 row(s) affected)

您现在可以使用CROSS APPLY来拆分您表中的每一行，例如：

DECLARE @YourTable table (RowID int, RowValue varchar(8000))
INSERT INTO @YourTable VALUES (1,'1. Our function has defined how Availability is measured the hardware/software in Production;#3#2. Availability threshold levels exist for our function (e.g., SLA''s);#3#3. Our function follows a defined process when there are threshold breaches;#4#4. Our function collects and maintains Availability data;#4#5. Comparative analysis helps identify trending with the Availability data;#4#6. Operating Level Agreements (OLA''s) guide our interaction with other internal teams;#4#')
INSERT INTO @YourTable VALUES (2,'1. one;#1#2. two;#2#3. three;#3#')
INSERT INTO @YourTable VALUES (3,'1. aaa;#1#2. bbb;#2#3. ccc;#3#')

;WITH AllRows As
(
SELECT
    o.RowID,st.RowNumber,st.ListValue AS RowValue
    FROM @YourTable  o
        CROSS APPLY  dbo.FN_ListToTableRows('#',LEFT(o.RowValue,LEN(o.RowValue)-1)) AS st
)
SELECT
    a.RowID,a.RowValue AS Question, b.RowValue AS Answer
    FROM AllRows                  a
        LEFT OUTER JOIN   AllRows b ON a.RowID=b.RowID AND a.RowNumber+1=b.RowNumber
    WHERE a.RowNumber % 2 = 1 

输出：

RowID       Question                                                                                        Answer
----------- ----------------------------------------------------------------------------------------------- -------
1           1. Our function has defined how Availability is measured the hardware/software in Production;   3
1           2. Availability threshold levels exist for our function (e.g., SLA's);                          3
1           3. Our function follows a defined process when there are threshold breaches;                    4
1           4. Our function collects and maintains Availability data;                                       4
1           5. Comparative analysis helps identify trending with the Availability data;                     4
1           6. Operating Level Agreements (OLA's) guide our interaction with other internal teams;          4
2           1. one;                                                                                         1
2           2. two;                                                                                         2
2           3. three;                                                                                       3
3           1. aaa;                                                                                         1
3           2. bbb;                                                                                         2
3           3. ccc;                                                                                         3

(12 row(s) affected)

- KM.

---

感谢您的回复KM，最终我选择了stander的答案。 - Hank Stallings

Answer 2

好的，让我们看看。我不得不使用游标，因为这可能更适合使用像C#这样的编程语言来实现，但是我们可以尝试一下... 使用Sql Server 2005，请尝试以下操作。如果您需要任何解释，请告诉我。

DECLARE @Table TABLE(
        QuestionSource VARCHAR(50),
        QA VARCHAR(1000)
)

DECLARE @ReturnTable TABLE(
        QuestionSource VARCHAR(50),
        Question VARCHAR(1000),
        Answer VARCHAR(10)
)

DECLARE @XmlField XML,
        @QuestionSource VARCHAR(40)

INSERT INTO @Table SELECT
'Availability','1. Our function has defined how Availability is measured the hardware/software in Production;#3#2. Availability threshold levels exist for our function (e.g., SLA''s);#3#3. Our function follows a defined process when there are threshold breaches;#4#4. Our function collects and maintains Availability data;#4#5. Comparative analysis helps identify trending with the Availability data;#4#6. Operating Level Agreements (OLA''s) guide our interaction with other internal teams;#4#'
INSERT INTO @Table SELECT
'Capacity', '1. Our function has defined how Availability is measured the hardware/software in Production;#1#2. Availability threshold levels exist for our function (e.g., SLA''s);#2#3. Our function follows a defined process when there are threshold breaches;#3#4. Our function collects and maintains Availability data;#4#5. Comparative analysis helps identify trending with the Availability data;#5#6. Operating Level Agreements (OLA''s) guide our interaction with other internal teams;#6#'


DECLARE Cur CURSOR FOR
SELECT  QuestionSource,
        CAST(Val AS XML) XmlVal
FROM    (
            SELECT  QuestionSource,
                    LEFT(Vals, LEN(Vals) - LEN('<option><q>')) Val
            FROM    (
                        SELECT  QuestionSource,
                                '<option><q>' + REPLACE(REPLACE(QA, ';#','</q><a>'), '#', '</a></option><option><q>') Vals
                        FROM    @Table
                    ) sub
        ) sub

OPEN Cur
FETCH NEXT FROM Cur INTO @QuestionSource, @XmlField

WHILE @@FETCH_STATUS = 0 
BEGIN
    INSERT INTO @ReturnTable
    SELECT  @QuestionSource,
            T.split.query('q').value('.', 'nvarchar(max)') question,
            T.split.query('a').value('.', 'nvarchar(max)') answer
    FROM    @XmlField.nodes('/option') T(split)
    FETCH NEXT FROM Cur INTO @QuestionSource, @XmlField
END

CLOSE Cur
DEALLOCATE Cur

SELECT  * 
FROM    @ReturnTable

- Adriaan Stander

---

astander - 非常感谢，这正是我需要的。 - Hank Stallings

astander - 现在，如果我有多列这些问题，我可以使用UNION从其他列中提取问题。因此，一列代表“可用性”，下一列代表“容量”，我如何添加另一列来确定数据来自哪个问题和答案集？ - Hank Stallings

astander - 我已经将我的代码添加到了问题底部。我该如何编辑它以包括QuestionSource列？ - Hank Stallings

你的示例中 QuestionSource 在哪里？ - Adriaan Stander

没事了...我重新看了你的代码，成功修复了我的代码。我应该在这里发布我最终得出的代码吗？ - Hank Stallings

如果没有任何答案完全符合您的需求，那么是的，您可以自己回答问题，但请将其保留在您的问题中，不要创建一个答案。X-) - Adriaan Stander