# CTE Recursive Query

### Question

• Hello

I have a sentence

This dog, which barks all the time, is getting on my nerves - not (it's only a joke).

I would like to use a recursive CTE to split the sentence at comma and hypen and brackets.

Output at this stage
This dog
which barks all the time
is getting on my nerves
not
it's only a joke

Then in the same CTE query I would like to extract each word from the output above.

thanks
Wednesday, March 17, 2010 11:26 AM

• thanks

but I need to use a recursive CTE.

I also need to extract each word from the list that is produced.

thanks again
declare @sep char(1), @s varchar(512)
select    @s = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'
SELECT    @s = REPLACE(REPLACE(REPLACE(@s,'-',','),'(',','),')',','),
@sep = ',';

WITH Pieces(pn, start, [stop]) AS
(
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces

• Marked as answer by Friday, March 19, 2010 10:52 AM
Wednesday, March 17, 2010 1:01 PM

### All replies

• Hello

I have a sentence

This dog, which barks all the time, is getting on my nerves - not (it's only a joke).

I would like to use a recursive CTE to split the sentence at comma and hypen and brackets.

Output at this stage
This dog
which barks all the time
is getting on my nerves
not
it's only a joke

Then in the same CTE query I would like to extract each word from the output above.

thanks
Hi,

try below code

set nocount on
declare @inputdata varchar(max),
@seperator nvarchar(5)

declare @seperatedData TABLE
(
id int identity(1,1),
data nvarchar(4000)
)

select @inputdata = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'

SELECT @inputdata = REPLACE(REPLACE(REPLACE(@inputdata,'-',','),'(',','),')',','),
@seperator = ','

DECLARE @Cnt int
SELECT    @Cnt = 1

WHILE (Charindex(@seperator,@inputdata)>0)
BEGIN
print ltrim(rtrim(Substring(@inputdata,1,Charindex(@seperator,@inputdata)-1)))
SELECT    @inputdata = Substring(@inputdata,Charindex(@seperator,@inputdata)+1,len(@inputdata))
SELECT    @Cnt = @Cnt + 1
END

Thanks,
Mayur

Wednesday, March 17, 2010 11:57 AM
• alternative solution is given below

CREATE FUNCTION [dbo].[udf_GetSplitText]
(
@inputdata nvarchar(4000),
@seperator nvarchar(5)

RETURNS @seperatedData TABLE
(
id int identity(1,1),
data nvarchar(4000)
)

AS
BEGIN
DECLARE @Cnt int
SELECT    @Cnt = 1

WHILE (Charindex(@seperator,@inputdata)>0)
BEGIN
INSERT INTO @seperatedData (data)
SELECT    data = ltrim(rtrim(Substring(@inputdata,1,Charindex(@seperator,@inputdata)-1)))

SELECT    @inputdata = Substring(@inputdata,Charindex(@seperator,@inputdata)+1,len(@inputdata))
SELECT    @Cnt = @Cnt + 1
END

INSERT INTO @seperatedData (data)
SELECT data = ltrim(rtrim(@inputdata))

RETURN
END

go
declare @t varchar(max)
select @t = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'

SELECT @T = REPLACE(REPLACE(REPLACE(@T,'-',','),'(',','),')',',')

select id,data from  dbo.udf_GetSplitText(@t,',')

Wednesday, March 17, 2010 12:00 PM
• thanks

but I need to use a recursive CTE.

I also need to extract each word from the list that is produced.

thanks again
Wednesday, March 17, 2010 12:30 PM
• thanks

but I need to use a recursive CTE.

I also need to extract each word from the list that is produced.

thanks again
declare @sep char(1), @s varchar(512)
select    @s = 'This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'
SELECT    @s = REPLACE(REPLACE(REPLACE(@s,'-',','),'(',','),')',','),
@sep = ',';

WITH Pieces(pn, start, [stop]) AS
(
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces

• Marked as answer by Friday, March 19, 2010 10:52 AM
Wednesday, March 17, 2010 1:01 PM
• ANOTHER ALTERNATIVE WAY

declare @a varchar(1000)
set @a='This dog, which barks all the time, is getting on my nerves - not (it''s only a joke)'
SELECT    @A = REPLACE(REPLACE(REPLACE(REPLACE( @a,  ',' , '</M><M>'),'-','</M><M>'),'(','</M><M>'),')','</M><M>')

;WITH Cte AS
(
SELECT
CAST('<M>' + @A + '</M>' AS XML)  as id
)
select
Split.a.value('.', 'VARCHAR(100)') AS id
FROM Cte
CROSS APPLY id.nodes('/M') Split(a)

Thanks,
Mayur
Thursday, March 18, 2010 6:29 AM
• thanks
Friday, March 19, 2010 10:53 AM