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)
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?
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
—————————————————-
That IS using reverese! Duh! :o)
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)
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.
Pinal, this does not seem to be a true solution for the said problem. as the UDF uses the REVERSE here.
By the way, Pinal, there is missed comma after @startposition and before LEN() in STUFF function.
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
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'
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'
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.