(@pString VARCHAR(8000), @pDelimiter VARCHAR(16))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

-- enough to cover VARCHAR(8000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT 1 UNION ALL

SELECT t.N+ Len( @pDelimiter ) FROM cteTally t WHERE SUBSTRING(@pString,t.N, Len( @pDelimiter ) ) = @pDelimiter

),

cteLen(N1,L1) AS(--==== Return start and length (for use in substring)

SELECT s.N1,

ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1 ,8000)

FROM cteStart s

)

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),

Item = SUBSTRING(@pString, l.N1, l.L1)

FROM cteLen l;

declare @QandAs as Table ( QandAId Int Identity, QandA NVarChar(1000) );

insert into @QandAs ( QandA ) values

( '<b>Q1a:</b><br />A1a<br /><br /><b>Q2a:</b><br />A2a<br /><br /><b>Q3a:</b><br />A3a<br /><br /><b>Q4a:</b><br />A4a<br /><br />' ),

( '<b>Q1b:</b><br />A1b<br /><br /><b>Q2b:</b><br />A2b<br /><br /><b>Q3b:</b><br />A3b<br /><br /><b>Q4b:</b><br />A4b<br /><br />' );

select * from @QandAs;

with QAPairs as (

select QandAId, ItemNumber, Item, Row_Number() over ( partition by QandAId order by ItemNumber desc ) as RN

from @QandAs cross apply

dbo.DelimitedSplit8K( QandA, '<br /><br />' ) )

select QandAId, ItemNumber, Item, RN

from QAPairs

where RN > 1 -- Eliminate the extraneaous empty Q/A pair at the end of the string.

order by QandAId, ItemNumber;

with QAPairs as (

select QandAId, ItemNumber as QuestionNumber, Item as QA, Row_Number() over ( partition by QandAId order by ItemNumber desc ) as RN

from @QandAs cross apply

dbo.DelimitedSplit8K( QandA, '<br /><br />' ) )

select QandAId, QuestionNumber, QA, ItemNumber, Item, case when ItemNumber % 2 = 1 then 'Q' else 'A' end as 'Q/A'

from QAPairs cross apply

dbo.DelimitedSplit8K( QA, '<br />' )

where RN > 1 -- Eliminate the extraneaous empty Q/A pair at the end of the string.

order by QandAId, QuestionNumber, ItemNumber;