Solution to TSQL Beginners Challenge 23

DECLARE @t TABLE(        
Data VARCHAR(20),         
DataToReplace VARCHAR(100),         
ReplacedWithData VARCHAR(100) 
) 
INSERT INTO @t  
select  'Cap','cp','xy'			UNION ALL
Select	'Phanindra','ahir','jukl' UNION ALL
Select	'blanket','co','pq'		UNION ALL -- no change to data
Select	'Lamp','Le','PR'		

;With myCTE1
As
(
	SELECT Data,Traslate=cast(Data as VARCHAR(20)),l=1,DataToReplace,ReplacedWithData From @t
	UNION ALL
	SELECT Data,Traslate=CAST(CASE WHEN l<=len(Data) Then Replace(Traslate,substring(DataToReplace,l,1),substring(ReplacedWithData,l,1)) END AS VARCHAR(20)) 
	,l=l+1,DataToReplace,ReplacedWithData FROM myCTE1 WHERE  Traslate IS NOT NULL
)
SELECT Data,DataToReplace,ReplacedWithData,Traslate From  myCTE1 where l=LEN(Data)
Order by Data

Tags: