SQL Trivia – Reverse a string without using T-SQL REVERSE() function
Everyone knows that the REVERSE() function (as its name suggests) reverses a string’s value, MSDN link.
This post’s title talks about going the other way, but why? If we already have this function then why to reinvent the wheel.
But at times an Interviewer may trick you and asks this question randomly: “How will you reverse a string without using REVERSE() function? No loop, should be a single query.” People who have actually worked on CTEs and know about recursive CTEs can guess the answer and create the query on-spot. Actually he wants to know if you have really worked on CTEs & recursive CTEs, or just learned the concept, or do not know about this at all.
Let’s check this by a simple example:
DECLARE @StringToReverse VARCHAR(55)
SET @StringToReverse = 'Reverse a string with out using REVERSE() function'
;WITH cte AS (
SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln
UNION ALL
SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
FROM cte
WHERE ln >= 1)
SELECT @StringToReverse AS String, revStr
FROM cte
WHERE ln = 0
Output:-
Note: In the code above check line numbers 5 & 7 containing CAST function, “CAST(” AS VARCHAR(55)) AS revStr” & “CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55))”.
Applying CAST or CONVERT function is necessary here as datatype & size of both the columns in the anchor & recursive part should be same. Otherwise SQL server will throw the following error:
Msg 240, Level 16, State 1, Line 4
Types don’t match between the anchor and the recursive part in column “revStr” of recursive query “cte”.
>> Check & Subscribe my [YouTube videos] on SQL Server.
Leave a Reply
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 
Football Team Location Problem | SQL Lion
T – SQL challenge to solve Football Team Location Problem in a single query.
http://www.sqllion.com/2011/09/football-team-location-problem/
It would be more helpful if there is more explanation on how the query will get executed (The logic in using recursive With clause)
Reverse a string without Reverse function and while loop
http://www.sqlqueries.in/2016/06/reverse-a-string-without-using-sql-reverse-function.html
DECLARE @revString VARCHAR(55)=”
DECLARE @string2 VARCHAR(55)=’Reverse string without REVERSE() function’
DECLARE @ln INT=LEN(@string2)
WHILE @ln > 0
BEGIN
SET @revString = @revString + SUBSTRING(@string2, @ln, 1)
SET @ln= @ln – 1
END
SELECT @string2, @revString,@ln
declare @s varchar(10)
set @S = ‘Jamie’
select substring(@s,len(@S)- n.number,1)AS [text()]
from
(SELECT number
FROM master..spt_values
WHERE
type = ‘P’
AND number <= 10)n
For XML PATH (”)
DECLARE @Str Varchar(100)=’12345′
;
WITH CTE
AS
(
SELECT LEN(@Str) AS L
UNION ALL
SELECT L-1 FROM CTE
WHERE L-1 >0
)
SELECT (
SELECT SUBSTRING(@Str,L,1) FROM CTE
FOR XML PATH(”)
)
Given a string, that contains special character together with alphabets ( a to z and A to Z ), reverse the string in a way that special characters are not affected.