April 14, 2009 at 6:40 pm
Hello all.
I am trying to write a function which reverses passed string WITHOUT using any built-in functions
So if 'abc' is passed, it returns 'cba'
I want to use recursion. Here is what I got:
create function StringReverse
(
@InString varchar(20)
)
returns varchar(20)
AS
begin
declare @RevString varchar(20)
IF len(@InString) in (0,1)
set @RevString = @InString
ELSE
set @RevString =
(
dbo.StringReverse(substring(@InString, len(@InString)/2+1, len(@InString))
+
dbo.StringReverse(substring(@InString, 1, len(@InString)/2)))
)
return @RevString
end
It compiles fine, but when I call it, it throws an exception:
select dbo.StringReverse('abc')
Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Why? Function calls itself at most 5 times:
Pass1 - RevString('c') + RevString('ab')
Pass2 - 'c' + (RevString(RevString('b') + RevString('a'))
Pass3 - 'cba'
Should I use a loop?
Thanks in advance for your help!
Pit
April 14, 2009 at 6:45 pm
yeah, i think you'll need to use a loop from 1 to datalength(@inputstring), and appending the char in reverse order.
recursively calling a string that fiddles with one or two chars is going to hit the issue you already identified, max number of nesting levels.
Lowell
April 14, 2009 at 7:00 pm
Hmmm...
Does it mean that recursion cannot be used in this example? Or it should be implemented differently?
Thanks,
Pit
April 14, 2009 at 7:45 pm
pshvets (4/14/2009)
Hmmm...Does it mean that recursion cannot be used in this example? Or it should be implemented differently?
Thanks,
Pit
recursion has it's place, of course, but it does not serve string manipulation very well.
without using a tally table, i'd do it like this: just a simple loop and string concatenation:
ALTER function CharReversal(@inputstring varchar(max))
returns varchar(max)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @i int,
@Results varchar(max)
SET @Results=''
SET @i = 1
WHILE @i <= DATALENGTH(@inputstring)
BEGIN
SET @Results = SUBSTRING(@inputstring,@i,1) + @Results
SET @i=@i + 1
END
RETURN @Results
END
select dbo.CharReversal('abc123xyz')
--Results:zyx321cba
Lowell
April 14, 2009 at 8:06 pm
Here's the code that I sent to Jacob Sebastian's challenge last month. Minus the two syntax errors that probably eliminated me from consideration 🙁 (gawd, I need a vacation!):
/* TSQL Challenge #3
Tested on SQL Server 2008
By RBarryYoung, March 28, 2009
*/
DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(MAX))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
;WITH cteReverseRecur as (
Select ID
, RIGHT( data, 1 ) as RevStr
, LEFT( data, LEN([data])-1 ) as RemStr
From @t
UNION ALL
Select ID
, RevStr + RIGHT( RemStr, 1 )
, Left( RemStr, LEN(RemStr)-1 )
From cteReverseRecur
Where RemStr > '')
SELECT ID, RevStr as data
From cteReverseRecur
Where RemStr = '';
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 15, 2009 at 10:40 am
Lowell (4/14/2009)
pshvets (4/14/2009)
Hmmm...Does it mean that recursion cannot be used in this example? Or it should be implemented differently?
Thanks,
Pit
recursion has it's place, of course, but it does not serve string manipulation very well.
without using a tally table, i'd do it like this: just a simple loop and string concatenation:
ALTER function CharReversal(@inputstring varchar(max))
returns varchar(max)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @i int,
@Results varchar(max)
SET @Results=''
SET @i = 1
WHILE @i <= DATALENGTH(@inputstring)
BEGIN
SET @Results = SUBSTRING(@inputstring,@i,1) + @Results
SET @i=@i + 1
END
RETURN @Results
END
select dbo.CharReversal('abc123xyz')
--Results:zyx321cba
Thank you for suggestion.
I am still trying to figure out how to use recursion in this example. I am reading Joe Celko's book "Trees and Hierarchies in SQL for Smarties" and trying to write in T-SQL what he wrote for Oracle sql.
Thanks,
Pit.
April 15, 2009 at 11:40 am
FYI, my example is recursive.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 15, 2009 at 12:05 pm
Here's the sampe I came up with.
declare @STR varchar(100), @StrRev varchar(100);
select @STR = 'able was I ere I saw elba';
select @StrRev = coalesce(@StrRev + substring(@Str, number, 1), substring(@Str, number, 1))
from dbo.Numbers
where number between 1 and len(@Str)
order by number desc;
select @STR, @StrRev;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 12:06 pm
pshvets (4/14/2009)
Hello all.I am trying to write a function which reverses passed string WITHOUT using any built-in functions
So if 'abc' is passed, it returns 'cba'
I want to use recursion. Here is what I got:
create function StringReverse
(
@InString varchar(20)
)
returns varchar(20)
AS
begin
declare @RevString varchar(20)
IF len(@InString) in (0,1)
set @RevString = @InString
ELSE
set @RevString =
(
dbo.StringReverse(substring(@InString, len(@InString)/2+1, len(@InString))
+
dbo.StringReverse(substring(@InString, 1, len(@InString)/2)))
)
return @RevString
end
It compiles fine, but when I call it, it throws an exception:
select dbo.StringReverse('abc')
Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Why? Function calls itself at most 5 times:
Pass1 - RevString('c') + RevString('ab')
Pass2 - 'c' + (RevString(RevString('b') + RevString('a'))
Pass3 - 'cba'
Should I use a loop?
Thanks in advance for your help!
Pit
The requirement of “Reverse string without built in functions” doesn’t make any sense, especially since all of the solutions posted, including yours, use built in functions.
If it’s OK to use the built in DATALENGTH, SUBSTRING, RIGHT, LEFT, or LEN functions, why can’t you just use the built in REVERSE function?
April 15, 2009 at 12:12 pm
I had that same point in my original post, but it got lost when my browser crashed mid-post. I think it's just a game to not use Reverse. Quite possibly a homework question, since they are often written with that kind of arbitrary rule.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 1:53 pm
This is actually from Jacob Sebastiens Challenge #3, where the actual rules were: "Write a single query that can reverse the strings in this column without using the REVERSE() function".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 15, 2009 at 3:48 pm
My question is not a homework question. 🙂
My background is BI Development on Oracle, Teradata, Unix platforms. I am simply learning MS SQL Server and T-SQL.
And thanks for all your help!
Pit.
April 15, 2009 at 4:09 pm
Maybe you should learn to use what is available in SQL Server, like REVERSE, instead of spending time duplicating what is already available.
April 16, 2009 at 7:01 am
Michael Valentine Jones (4/15/2009)
Maybe you should learn to use what is available in SQL Server, like REVERSE, instead of spending time duplicating what is already available.
Nah. One of the best ways to learn the tricky parts of any engineering tool (and programming languages are definitely engineering tools) is to work out how to do something without using a pre-built answer.
Nails and screws are awefully convenient, but it's fun and educational to build a wooden cabinet without using any. Take away wood glue also, and it's a challenge for even a good woodworker, and fun. And it teaches you techniques you can use in other applications that might be more practical.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2009 at 7:53 am
GSquared (4/16/2009)
Michael Valentine Jones (4/15/2009)
Maybe you should learn to use what is available in SQL Server, like REVERSE, instead of spending time duplicating what is already available.Nah. One of the best ways to learn the tricky parts of any engineering tool (and programming languages are definitely engineering tools) is to work out how to do something without using a pre-built answer.
Nails and screws are awefully convenient, but it's fun and educational to build a wooden cabinet without using any. Take away wood glue also, and it's a challenge for even a good woodworker, and fun. And it teaches you techniques you can use in other applications that might be more practical.
Pshaw! That's no challenge, just use duct tape.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply