Thursday, July 2, 2009

Solution for TSQL Challenge 3-Reverse Without reverse

Hi.
My idle days lead me to solve another puzzle from Jacob.Which is on http://beyondrelational.com/blogs/tc/archive/2009/05/24/tsql-challenge-3.aspx.
in this challange i need to reverse string without sql Reverse Function.to solve this i use CTE .the great feature provided by sql server.

GivenData:

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'


Solution:

;WITH FirstTable AS
(
SELECT ID,Position=LEN(data),data,CAST(SUBSTRING(data,LEN(data)+1,1) AS VARCHAR(200)) AS ReverseData FROM @t
UNION ALL
SELECT ID,Position=Position-1,data,CAST(ReverseData+SUBSTRING(data,Position,1) AS VARCHAR(200)) AS ReverseData FROM FirstTable
WHERE Position>0
)
SELECT * FROM FirstTable WHERE LEN(ReverseData)=LEN(data) ORDER BY id desc


Hope this will help you.

Thanks.