T-SQL:解析带有多个定界符的字符串 [英] T-SQL: Parsing String with Multiple delimiters

问题描述

我需要能够在SharePoint数据库中查询调查结果.我遇到问题的数据类型是"Rating Scale"值.因此，每个表格列中的数据代表了一组子问题及其答案.

I need to be able to query a SharePoint database for survey results. The type of data I'm having problems with is a "Rating Scale" value. So the data in each table column represents a whole group of sub-questions and their answers.

因此，以下是在一"列中找到的示例:

So the following is an example of what is found in ONE column:

1.我们的功能定义了如何在生产环境中测量可用性的硬件/软件；#3#2.我们的功能(例如SLA)存在可用性阈值级别；#3#3.当存在阈值违反时，我们的功能将按照定义的过程进行；#4#4.我们的功能收集并维护可用性数据；#4#5.比较分析有助于通过可用性数据确定趋势；#4#6.操作级别协议(OLA)指导我们与其他内部团队的互动；#4#

问题以分号结尾，并且答案在两个#号内.因此，第一个问题的答案是3.

The Questions end with a semi-colon and their answers are inside the two # signs. So the answer to the first question is 3.

当我导出调查结果时，它会将每个问题格式化为列标题，并将答案格式化为下面单元格中的值，这是获取每个问题平均值的理想选择，并且希望能够复制该问题通过SQL查询.

When I export the results of the survey it formats each question as a column header and the answer as the value in the cell below, which is ideal to get an average for each question, and would love to be able to replicate that from a SQL query.

但是，如果我能将查询结果分成两列(问题，答案)...我会很高兴的.

But if I could get query results into two columns (Question, Answer)...I'd be thrilled with that.

感谢您的帮助.

非常感谢

汉克档位

*****附录:**

*****ADDENDUM:**

这是我的astander解决方案版本...再次感谢！

This was my version of astander's solution...THANKS again!

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 

推荐答案

好的，让我们看看.我必须使用游标，因为使用C#这样的编程语言可能会更好地实现，但是这里...使用Sql Server 2005，请尝试以下操作.让我知道您是否需要任何解释.

OK, let see. I had to use a cursor, as this would probably have been better achieved from a programming language like C#, but here goes... Using Sql Server 2005, try the following. Let me know if you need any explanations.

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

这篇关于T-SQL:解析带有多个定界符的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！