Updated 20200725
No cursors, loops OR recursive CTEs required. A cursor or recursive solution will almost certainly be slow and overly bulky. A set-based solution, leveraging DelimitedSplit8K or using patextract8K is the way to go.
Note the updated sample data (which includes more @'s):
-- 1. Sample Data
declare @question table
(
qID int PRIMARY KEY CLUSTERED,
question nvarchar(200) NOT NULL
);
declare @answers table
(
aID int NOT NULL INDEX nc__answers__aID,
key_ nvarchar(30) NOT NULL,
value_ nvarchar(30) NOT NULL
);
insert into @question (qID,question) values
(1,'I have a question. @@What is your name@@@@what is your age@@I need more answers.'+
'@@@@@@@@@@@@@@@@@@@How are you@@@@@@@@@@@@@@@@@@@@@@@@@@@.Answer if you want.');
insert into @answers (aID,key_, value_) values
(1,'What is your name','Doe'),(1,'what is your age','29'),(1,'How are you','I am fine');
-- 2. delimitedSplit8K example
SELECT FinalAnswer =
(
SELECT ISNULL(a.value_,split.item)
FROM @question AS q
CROSS APPLY dbo.delimitedSplit8K(q.question,'@') AS split
LEFT JOIN @answers AS a
ON a.aID = q.qID
AND a.key_ = split.item
ORDER BY split.ItemNumber
FOR XML PATH('')
);
-- 3. Patextract8K example
SELECT FinalAnswer =
(
SELECT ISNULL(a.value_,split.item)
FROM @question AS q
CROSS APPLY samd.patExtract8K(q.question,'@') AS split
LEFT JOIN @answers AS a
ON a.aID = q.qID
AND a.key_ = split.item
ORDER BY split.ItemNumber
FOR XML PATH('')
);
Results for each solution:
FinalAnswer
--------------------------------------------------------------------------
I have a question. Doe29I need more answers.I am fine.Answer if you want.
The benefit of the extractor method over the splitter method is that the extractor method "skips" the delimiters (@). Compare the execution plans:
DelimitedSplit8K Execution plan
patExtract8K Execution plan
I added some extra delimiters in the sample data to show how, using the extractor it extracts the 6 required items and begins processing only six rows moving forward (instead of 55).
Note that, in this case, we are dealing with only one record. To better understand how to do this with multiple rows you would need to have better sample data.
Final Edit 20200726 (Performance Test):
Based on a discussion in this thread. I included two different splitters which could be used for this and tested them against a bunch of rows leveraging both a serial and parallel execution plan.
Sample Data
-- 1. Sample Data
SET NOCOUNT ON;
IF OBJECT_ID('dbo.question','U') IS NOT NULL DROP TABLE dbo.question;
IF OBJECT_ID('dbo.answers','U') IS NOT NULL DROP TABLE dbo.answers;
CREATE TABLE dbo.question
(
qID int PRIMARY KEY CLUSTERED,
question nvarchar(200) NOT NULL
);
CREATE TABLE dbo.answers
(
aID int NOT NULL INDEX nc__answers__aID,
key_ nvarchar(30) NOT NULL,
value_ nvarchar(30) NOT NULL
);
insert into dbo.question (qID,question) values
(1,'I have a question. @@What is your name@@@@what is your age@@I need more answers.'+
'@@@@@@@@@@@@@@@@@@@How are you@@@@@@@@@@@@@@@@@@@@@@@@@@@.Answer if you want.'),
(2,'I have a question too.@@@@@@@@@@@@Your Name is?@@@how young are you?@@Answer This-'+
'@@@@@@@@@@@You good?@@@@@@@@@@@@@@@How is Bill@@@@@@@@Answer if you must!.');
insert into dbo.answers (aID,key_, value_) values
(1,'What is your name','Doe'),(1,'what is your age','29'),(1,'How are you','I am fine'),
(2,'Your Name is?','Fred'),(2,'how young are you?','38'),(2,'Answer This-','What?'),
(2,'You good?','Yeah!'),(2,'How is Bill','Sleepy');
-- 2. Add Many New rows for performance testing
DECLARE @multiplier BIGINT = 10000;
INSERT dbo.question
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+2, q.question
FROM dbo.question AS q
CROSS JOIN core.rangeAB(1,@multiplier,1,1) AS r;
INSERT dbo.answers
SELECT a.aID%2+((a.aID+r.RN)*2), a.key_, a.value_
FROM dbo.answers AS a
CROSS JOIN core.rangeAB(1,@multiplier,1,0) AS r;
Performance Test
-- 3. Performance Test
BEGIN
PRINT CHAR(10)+'Start Test'+CHAR(10)+REPLICATE('-',90);
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2;
IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3;
IF OBJECT_ID('tempdb..#t4') IS NOT NULL DROP TABLE #t4;
PRINT CHAR(10)+'delimitedSplit8K serial'+CHAR(10)+REPLICATE('-',90);
WITH q2 AS (SELECT q.qID FROM dbo.question AS q GROUP BY q.qID)
SELECT
q2.qID,
FinalAnswer = fa.FinalAnswer
INTO #t1
FROM q2
CROSS APPLY
(
SELECT ISNULL(a.value_,split.item)
FROM (SELECT q.* FROM dbo.question AS q WHERE q.qID = q2.qID) AS q
CROSS APPLY dbo.delimitedSplit8K(q.question,'@') AS split
LEFT JOIN (SELECT a.* FROM dbo.answers AS a WHERE a.aID = q2.qID) AS a
ON a.aID = q.qID
AND a.key_ = split.item
ORDER BY split.ItemNumber
FOR XML PATH('')
) AS fa(FinalAnswer)
OPTION (MAXDOP 1);
PRINT CHAR(10)+'delimitedSplit8K parallel'+CHAR(10)+REPLICATE('-',90);
WITH q2 AS (SELECT q.qID FROM dbo.question AS q GROUP BY q.qID)
SELECT
q2.qID,
FinalAnswer = fa.FinalAnswer
INTO #t2
FROM q2
CROSS APPLY
(
SELECT ISNULL(a.value_,split.item)
FROM (SELECT q.* FROM dbo.question AS q WHERE q.qID = q2.qID) AS q
CROSS APPLY dbo.delimitedSplit8K(q.question,'@') AS split
LEFT JOIN (SELECT a.* FROM dbo.answers AS a WHERE a.aID = q2.qID) AS a
ON a.aID = q.qID
AND a.key_ = split.item
ORDER BY split.ItemNumber
FOR XML PATH('')
) AS fa(FinalAnswer)
OPTION (QUERYTRACEON 8649);
PRINT CHAR(10)+'patExtract8K Serial'+CHAR(10)+REPLICATE('-',90);
WITH q2 AS (SELECT q.qID FROM dbo.question AS q GROUP BY q.qID)
SELECT
q2.qID,
FinalAnswer = fa.FinalAnswer
INTO #t3
FROM q2
CROSS APPLY
(
SELECT ISNULL(a.value_,split.item)
FROM (SELECT q.* FROM dbo.question AS q WHERE q.qID = q2.qID) AS q
CROSS APPLY samd.patExtract8K(q.question,'@') AS split
LEFT JOIN (SELECT a.* FROM dbo.answers AS a WHERE a.aID = q2.qID) AS a
ON a.aID = q.qID
AND a.key_ = split.item
ORDER BY split.ItemNumber
FOR XML PATH('')
) AS fa(FinalAnswer)
OPTION (MAXDOP 1);
PRINT CHAR(10)+'patExtract8K Parallel'+CHAR(10)+REPLICATE('-',90);
WITH q2 AS (SELECT q.qID FROM dbo.question AS q GROUP BY q.qID)
SELECT
q2.qID,
FinalAnswer = fa.FinalAnswer
INTO #t4
FROM q2
CROSS APPLY
(
SELECT ISNULL(a.value_,split.item)
FROM (SELECT q.* FROM dbo.question AS q WHERE q.qID = q2.qID) AS q
CROSS APPLY samd.patExtract8K(q.question,'@') AS split
LEFT JOIN (SELECT a.* FROM dbo.answers AS a WHERE a.aID = q2.qID) AS a
ON a.aID = q.qID
AND a.key_ = split.item
ORDER BY split.ItemNumber
FOR XML PATH('')
) AS fa(FinalAnswer)
OPTION (QUERYTRACEON 8649);
END
Results:
delimitedSplit8K serial
------------------------------------------------------------------------------------------
SQL Server Execution Times: CPU time = 7641 ms, elapsed time = 7643 ms.
delimitedSplit8K parallel
------------------------------------------------------------------------------------------
SQL Server Execution Times: CPU time = 12454 ms, elapsed time = 2118 ms.
patExtract8K Serial
------------------------------------------------------------------------------------------
SQL Server Execution Times: CPU time = 4172 ms, elapsed time = 4216 ms.
patExtract8K Parallel
------------------------------------------------------------------------------------------
SQL Server Execution Times: CPU time = 7453 ms, elapsed time = 1343 ms.
For this, a set-based solution. Patextract works here because of the extra long delimiters. I tried the Recursive CTE solution below but could not get it to work; in part, because the logic would need to live in a function which is more work than I have time for. Nonetheless, rCTEs are slow for counting and for splitting strings (in most cases.)
