Reverse a string w/o built-in TSQL function

This time it will be fairly simple task of reversing a string in SQL without using REVERSE() function

Why we do it???

Because we can 🙂

First approach is traditional way  like below

declare @str varchar(50) ='live';
select @str String
declare @cnt int;
declare @revstr varchar(50);
set @cnt = len(ltrim(rtrim(@str)));
while (@cnt>0)
begin
set @revstr=concat(@revstr,substring(@str,@cnt,1))
set @cnt=@cnt-1;
end
select @revstr ReverseString

result

Lets do the same thing using CTE in different way

DECLARE @str VARCHAR(55)
SET @str = 'live'
 
;WITH cte AS (
 SELECT @str AS string, CAST('' AS VARCHAR(55)) AS ReverseString, LEN(@str) AS ln
 UNION ALL
 SELECT SUBSTRING(string,0,ln) AS string, CAST(ReverseString + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS ReverseString, ln-1 AS ln
 FROM cte
 WHERE ln >= 1)
SELECT @str AS String, ReverseString
FROM cte
WHERE ln = 0
resultnew

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s