Home > SQL Tips, SQL Trivia > SQL Trivia – Reverse a string without using T-SQL REVERSE() function

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
      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


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.

  1. September 19, 2011 at 6:53 pm

    Football Team Location Problem | SQL Lion
    T – SQL challenge to solve Football Team Location Problem in a single query.

  2. sriharsha
    April 14, 2016 at 11:34 pm

    It would be more helpful if there is more explanation on how the query will get executed (The logic in using recursive With clause)

  3. June 13, 2016 at 3:18 pm
  4. Arunachalam
    July 10, 2016 at 8:03 am

    DECLARE @revString VARCHAR(55)=”
    DECLARE @string2 VARCHAR(55)=’Reverse string without REVERSE() function’
    DECLARE @ln INT=LEN(@string2)
    WHILE @ln > 0
    SET @revString = @revString + SUBSTRING(@string2, @ln, 1)
    SET @ln= @ln – 1
    SELECT @string2, @revString,@ln

  5. Jamie Ingram
    August 14, 2017 at 1:13 pm

    declare @s varchar(10)
    set @S = ‘Jamie’

    select substring(@s,len(@S)- n.number,1)AS [text()]
    (SELECT number
    FROM master..spt_values
    type = ‘P’
    AND number <= 10)n
    For XML PATH (”)

  6. Shashi Pal Saini
    October 24, 2017 at 7:25 pm

    DECLARE @Str Varchar(100)=’12345′
    WHERE L-1 >0
    SELECT (

  7. November 13, 2019 at 4:27 am

    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.

  1. August 18, 2011 at 1:24 pm
  2. July 7, 2015 at 2:39 pm

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: