Question: Can you reverse a string, or its suffix, without using REVERSE? Yes. Keep the prefix and append the suffix’s characters from the end back to the chosen starting position.
At the last interview of the year, a co-interviewer gave a candidate an hour with SQL Server and the installed help files, but no internet. The candidate produced a useful extra feature: reversal could start at a selected position. There was one important catch, though. The answer used REVERSE, which the question had forbidden.
Let us keep that suffix idea and meet the restriction. This function is for a separate scratch database and refuses to overwrite an existing demo function.
IF OBJECT_ID(N'dbo.InterviewReverseSuffix',N'FN') IS NOT NULL
THROW 50001,'Use a fresh scratch database; the demo function already exists.',1;
GO
CREATE FUNCTION dbo.InterviewReverseSuffix
(@Text varchar(8000),@StartPosition int)
RETURNS varchar(8000)
AS
BEGIN
IF @Text IS NULL OR @StartPosition IS NULL RETURN NULL;
DECLARE @Length int=LEN(CONVERT(varchar(max),@Text)+CHAR(1))-1;
IF @StartPosition<=0 OR @StartPosition>@Length SET @StartPosition=1;
DECLARE @Result varchar(8000)=LEFT(@Text,@StartPosition-1),@Pos int=@Length;
WHILE @Pos>=@StartPosition
BEGIN
SET @Result=@Result+SUBSTRING(@Text,@Pos,1);
SET @Pos-=1;
END;
RETURN @Result;
END;
GO
SELECT dbo.InterviewReverseSuffix('forward string',3) AS FromThird,
dbo.InterviewReverseSuffix('forward string',0) AS WholeString,
dbo.InterviewReverseSuffix('forward string',-9) AS NegativeStart,
dbo.InterviewReverseSuffix('forward string',900) AS BeyondEnd;
SELECT '['+dbo.InterviewReverseSuffix('ab ',1)+']' AS PreservedSpaces;Starting at position 3 preserves fo and returns fognirts drawr. The original displayed forgnirts draw, which does not match its position-3 call. Starting at zero, a negative position or beyond the end returns the full reversal: gnirts drawrof.
Spaces and missing values are part of the answer
LEN normally ignores trailing spaces. The sentinel in the length expression counts them without changing the input, so 'ab ' becomes ' ba', shown between brackets in the second result. A NULL input or position returns NULL.
This preserves the original VARCHAR(8000) interface and simple-character exercise. It is not a general-purpose Unicode grapheme reversal routine. Combining characters and Unicode supplementary characters need a deliberately chosen representation and collation.
Without the interview restriction, SQL Server’s built-in REVERSE is the straightforward choice. The value of this exercise is reading the requirement carefully, defining boundary behavior and checking that the displayed result actually follows from the code.
The REVERSE reference describes the built-in alternative.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.
I agree, hence as mentioned, he failed this part of interview.
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.