T-SQL: Parsing String with Multiple delimiters.

Answer 1

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

Answer 2

You have to have a split function set up, but once you have it, try this cursor free solution:

I prefer the number table approach to split a string in TSQL

For this method to work, you need to do this one time table setup:

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)

Once the Numbers table is set up, create this split function, which WILL return empty strings and row 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 

You can now easily split a CSV string into a table and join on it, NOTE this split function returns empty strings and row numbers:

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

OUTPUT:

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

(11 row(s) affected)

Your can now use a CROSS APPLY to split every row in your table like:

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 

OUTPUT:

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)