November 30, 2015 at 6:49 am
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 = ''
November 30, 2015 at 7:36 am
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/
November 30, 2015 at 7:38 am
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
November 30, 2015 at 8:22 am
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
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply