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


Advertisements
  1. 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()]
    from
    (SELECT number
    FROM master..spt_values
    WHERE
    type = ‘P’
    AND number <= 10)n
    For XML PATH (”)

  2. 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
    BEGIN
    SET @revString = @revString + SUBSTRING(@string2, @ln, 1)
    SET @ln= @ln – 1
    END
    SELECT @string2, @revString,@ln

  3. June 13, 2016 at 3:18 pm
  4. 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)

  5. 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.
    http://www.sqllion.com/2011/09/football-team-location-problem/

  1. July 7, 2015 at 2:39 pm
  2. August 18, 2011 at 1:24 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 )

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

%d bloggers like this: