Interview Question of the Week #052 – Print String in Reverse Order

Last week, I attended the last interview of the year. One of my co-interviewer asked a coding question to the candidate. In this type of question, the candidate is given one hour with SQL Server with a help file. The computer does not have internet candidate can use all the help available from MSDN. In this case, the candidate was able to resolve the problem very successfully.

Question: Write a User Defined Function which can generate a script in reverse order without using the reverse function?

Answer: Here is the script for the user defined function which can generate script in the reversed order, (however, he failed at one condition where he used REVERSE function).

CREATE FUNCTION UDF_ReverseString
( @StringToReverse VARCHAR(8000),
@StartPosition INT)
RETURNS VARCHAR(8000)
AS
BEGIN
IF
(@StartPosition <= 0)
OR (
@StartPosition > LEN(@StringToReverse))
RETURN (REVERSE(@StringToReverse))
RETURN (STUFF (@StringToReverse,
@StartPosition,
LEN(@StringToReverse) - @StartPosition + 1,
REVERSE(SUBSTRING (@StringToReverse,
@StartPosition
LEN(@StringToReverse) - @StartPosition + 1))))
END
GO

What really impressed me was that it also allowed to specify from which position the string has to be reversed.

Reversing the string from third position
SELECT dbo.UDF_ReverseString('forward string',3)

Results Set : forgnirts draw

Reversing the entire string passing 0 as beginning character
SELECT dbo.UDF_ReverseString('forward string',0)

Results Set : gnirts drawrof

Reversing the entire string passing negative number as beginning character
SELECT dbo.UDF_ReverseString('forward string',-9)

Results Set : gnirts drawrof

Reversing the entire string passing larger number than string length as beginning character
SELECT dbo.UDF_ReverseString('forward string',900)

Results Set : gnirts drawrof

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Script: Finding Cumulative IO Per Database File
Next Post
SQL SERVER – Someone was trying to hack my SQL Server Logins in Azure!

Related Posts

No results found.

12 Comments. Leave new

  • but… there is a the reverse statement in the UDF function! so is it not supposed to be a failure instead of success? or I missed something in the question?

    Reply
  • How about with a WHILE loop?

    —————————————————-
    DECLARE @string varchar(10) = ‘Andrew’
    DECLARE @reverse varchar(10) = ”
    DECLARE @i int = len(@string)

    WHILE @i between 0 AND len(@string)
    begin
    set @reverse = @reverse + substring(@string,@i,1)
    set @i = @i-1
    print @i
    end
    SELECT @reverse
    —————————————————-

    Reply
  • That IS using reverese! Duh! :o)

    Reply
  • alter function fnReverse (@text varchar(500), @FromPosition int=0 )
    returns varchar(500)
    as
    begin
    declare @retext varchar(500)=”, @input varchar(500)=”
    set @input =substring(@text,0,@FromPosition)
    set @text=substring(@text,@fromPosition,len(@text)+1)
    while (len (@text)>0)
    begin
    set @retext=@retext+RIGHT(@text,1)
    set @text=LEFT(@text,len(@text)-1)

    end
    return @input+@retext
    end

    go
    select dbo.fnReverse(‘vikas’,default)

    Reply
  • This UDF uses the REVERSE function twice. Perhaps the interviewers were so dazzled with extra functionality that they didn’t notice the base requirement wasn’t met.

    Reply
  • Vaibhav Baweja
    January 4, 2016 12:38 pm

    Pinal, this does not seem to be a true solution for the said problem. as the UDF uses the REVERSE here.

    Reply
  • Arsen Barbakadze
    January 6, 2016 6:33 am

    By the way, Pinal, there is missed comma after @startposition and before LEN() in STUFF function.

    Reply
  • CREATE FUNCTION UDF_ReverseString
    ( @string VARCHAR(8000),
    @Pos INT)
    RETURNS VARCHAR(8000)
    AS
    BEGIN

    DECLARE @reverse varchar(8000) = ”

    DECLARE @i int = len(@string)

    SET @Pos = @Pos+1

    WHILE @i between @Pos AND len(@string)
    begin
    set @reverse = @reverse + substring(@string,@i,1)
    set @i = @i-1
    –print @i
    end

    Return @reverse

    END

    Reply
  • Truth is you don’t need a loop:

    declare
    @str varchar(1000)=’1234567890′,
    @rev_str varchar(1000)=”

    ;with nr as ( — generate numbers from 1 to 100,000;
    select top (100000)
    N = row_number() over (order by s1.[object_id])
    FROM
    sys.all_objects s1
    cross join sys.all_objects s2
    )
    select
    @rev_str=@rev_str+substring(@str,len(@str)-N+1,1)
    from
    nr
    where
    nr.n<=len(@str)

    select
    @str 'String',
    @rev_str 'Rev_String'

    Reply
  • Truth is you don’t need a loop:

    declare
    @str varchar(1000)=’1234567890′,
    @rev_str varchar(1000)=”

    ;with nr as ( — generate numbers from 1 to 100,000
    select top (100000)
    N = row_number() over (order by s1.[object_id])
    FROM
    sys.all_objects s1
    cross join sys.all_objects s2
    )
    select
    @rev_str=@rev_str+substring(@str,len(@str)-N+1,1)
    from
    nr
    where
    nr.n<=len(@str)

    select
    @str 'String',
    @rev_str 'Rev_String'

    Reply
  • I’m not sure what value a question like this provides. I’d prefer the candidate know that there is a a REVERSE function. Although the use of a numbers “table” by thesqllist does impress me.

    Reply

Leave a Reply

Menu