Help on understanding recursive cte

Question

Help on understanding recursive cte

squvi.87

Default port

Points: 1450
More actions
November 30, 2015 at 6:49 am

#302553

Hi Folks,
I just come across a code, but I cant understand how it is working
Please help me to understand the things.
SELECT 'Cap','cp','xy'
DECLARE @t TABLE(
Data VARCHAR(20),
DataToReplace VARCHAR(100),
ReplacedWithData VARCHAR(100)
)
INSERT INTO @t
SELECT 'Cap','cp','xy'
;
WITH CTE
AS
(
SELECT Data,REPLACE(Data,SUBSTRING(DataToReplace,1,1) ,SUBSTRING(ReplacedWithData,1,1)) X,STUFF(DataToReplace,1,1,'') y,
STUFF(ReplacedWithData,1,1,'') z FROM @t
UNION ALL
SELECT Data,REPLACE(X,SUBSTRING(Y,1,1) ,SUBSTRING(Z,1,1)),STUFF(Y,1,1,'') y,
STUFF(Z,1,1,'') FROM CTE
WHERE Y IS NOT NULL AND Y<>''
)
SELECT * FROM CTE
WHERE Y = ''

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

squvi.87 (11/30/2015)
Hi Folks,
I just come across a code, but I cant understand how it is working
Please help me to understand the things.
SELECT 'Cap','cp','xy'
DECLARE @t TABLE(
Data VARCHAR(20),
DataToReplace VARCHAR(100),
ReplacedWithData VARCHAR(100)
)
INSERT INTO @t
SELECT 'Cap','cp','xy'
;
WITH CTE
AS
(
SELECT Data,REPLACE(Data,SUBSTRING(DataToReplace,1,1) ,SUBSTRING(ReplacedWithData,1,1)) X,STUFF(DataToReplace,1,1,'') y,
STUFF(ReplacedWithData,1,1,'') z FROM @t
UNION ALL
SELECT Data,REPLACE(X,SUBSTRING(Y,1,1) ,SUBSTRING(Z,1,1)),STUFF(Y,1,1,'') y,
STUFF(Z,1,1,'') FROM CTE
WHERE Y IS NOT NULL AND Y<>''
)
SELECT * FROM CTE
WHERE Y = ''

Thanks for the code. Can you explain what part(s) of this you don't understand? There are a number of things going on there. Maybe you could share what the point of this code is?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

It replaces character by character in the column Data using DataToReplace and ReplacedWithData. One each recursion it replaces one character and removes one character from the auxiliar columns. In the end, it just select the second to last row after all the replacements are made.

I'm not sure if I made myself clear.

These are the steps for your example:

--Anchor query

SELECT Data,

REPLACE('Cap',SUBSTRING('cp',1,1), SUBSTRING('xy',1,1)) X,

STUFF('cp',1,1,'') y,

STUFF('xy',1,1,'') z

FROM @t;

--First recursion. This is the selected row

SELECT Data,

REPLACE('xap',SUBSTRING('p',1,1), SUBSTRING('y',1,1)) X,

STUFF('p',1,1,'') y,

STUFF('y',1,1,'') z

FROM @t

--Second/Last recursion. This row makes the recursion stop

SELECT Data,

REPLACE('xay',SUBSTRING('',1,1), SUBSTRING('',1,1)) X,

STUFF('',1,1,'') y,

STUFF('',1,1,'') z

FROM @t

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

This code sample might help too:

DECLARE

@data VARCHAR(20) = 'Cap',

@DataToReplace VARCHAR(100) = 'cp',

@ReplacedWithData VARCHAR(100) = 'xy'

;WITH Itally AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) d (n))

SELECT @data = REPLACE(@Data,SUBSTRING(@DataToReplace,n,1),SUBSTRING(@ReplacedWithData,n,1))

FROM iTally

WHERE n <= LEN(@DataToReplace)

SELECT @data

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden