Reverse string without built in functions

Question

Reverse string without built in functions

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

jcrawf02 (4/17/2009)
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.

Well, I've found that when you try to use duct tape to reverse a character string, it leaves bits of glue all over your hard drives, and that's a bit of a problem...

- 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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

check out the below link, This will give you the function to reverse a string with out using REVERSE.

http://blog.sqlauthority.com/2007/05/01/sql-server-user-defined-functions-udf-to-reverse-string-udf_reversestring/

I always prefer to use existing functions, if they are available instead of duplicating the function.. but again on the other had if we try to duplicate the logic it will increase our programming capabilities :;-)

I am confusing:hehe:

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

jcrawf02 (4/17/2009)
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.

Hmm, interesting idea... 🙂

[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]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Pinal Dave's reverse function has this code in it:

RETURN (REVERSE(@StringToReverse))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

OK, as long as we are doing useless SQL, I want to add numbers together without using any Arithmetic, Logical, Assignment, String, Bitwise, or Unary Operators or any Mathematical or Aggregate Functions.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Michael Valentine Jones (4/17/2009)
OK, as long as we are doing useless SQL, I want to add numbers together without using any Arithmetic, Logical, Assignment, String, Bitwise, or Unary Operators or any Mathematical or Aggregate Functions.

create table #Sums (

Num1 int not null,

Num2 int not null,

Sum12 int);

insert into #Sums (Num1, Num2, Sum12)

select 0, 0, 0 union all

select 1, 0, 1 union all

select 2, 0, 2 union all

select 3, 0, 3 union all

select 4, 0, 4 union all

select 5, 0, 5 union all

select 6, 0, 6 union all

select 7, 0, 7 union all

select 8, 0, 8 union all

select 9, 0, 9 union all

select 0, 1, 1 union all

select 1, 1, 2 union all

select 2, 1, 3 union all

select 3, 1, 4 union all

select 4, 1, 5 union all

select 5, 1, 6 union all

select 6, 1, 7 union all

select 7, 1, 8 union all

select 8, 1, 9 union all

select 9, 1, 10 union all

select 0, 2, 2 union all

select 1, 2, 3 union all

select 2, 2, 4 union all

select 3, 2, 5 union all

select 4, 2, 6 union all

select 5, 2, 7 union all

select 6, 2, 8 union all

select 7, 2, 9 union all

select 8, 2, 10 union all

select 9, 2, 11 union all

select 0, 3, 3 union all

select 1, 3, 4 union all

select 2, 3, 5 union all

select 3, 3, 6 union all

select 4, 3, 7 union all

select 5, 3, 8 union all

select 6, 3, 9 union all

select 7, 3, 10 union all

select 8, 3, 11 union all

select 9, 3, 12 union all

select 0, 4, 4 union all

select 1, 4, 5 union all

select 2, 4, 6 union all

select 3, 4, 7 union all

select 4, 4, 8 union all

select 5, 4, 9 union all

select 6, 4, 10 union all

select 7, 4, 11 union all

select 8, 4, 12 union all

select 9, 4, 13 union all

select 0, 5, 5 union all

select 1, 5, 6 union all

select 2, 5, 7 union all

select 3, 5, 8 union all

select 4, 5, 9 union all

select 5, 5, 10 union all

select 6, 5, 11 union all

select 7, 5, 12 union all

select 8, 5, 13 union all

select 9, 5, 14 union all

select 0, 6, 6 union all

select 1, 6, 7 union all

select 2, 6, 8 union all

select 3, 6, 9 union all

select 4, 6, 10 union all

select 5, 6, 11 union all

select 6, 6, 12 union all

select 7, 6, 13 union all

select 8, 6, 14 union all

select 9, 6, 15 union all

select 0, 7, 7 union all

select 1, 7, 8 union all

select 2, 7, 9 union all

select 3, 7, 10 union all

select 4, 7, 11 union all

select 5, 7, 12 union all

select 6, 7, 13 union all

select 7, 7, 14 union all

select 8, 7, 15 union all

select 9, 7, 16 union all

select 0, 8, 8 union all

select 1, 8, 9 union all

select 2, 8, 10 union all

select 3, 8, 11 union all

select 4, 8, 12 union all

select 5, 8, 13 union all

select 6, 8, 14 union all

select 7, 8, 15 union all

select 8, 8, 16 union all

select 9, 8, 17 union all

select 0, 9, 9 union all

select 1, 9, 10 union all

select 2, 9, 11 union all

select 3, 9, 12 union all

select 4, 9, 13 union all

select 5, 9, 14 union all

select 6, 9, 15 union all

select 7, 9, 16 union all

select 8, 9, 17 union all

select 9, 9, 18;

declare @Num1 int, @Num2 int;

select @Num1 = 1, @Num2 = 5;

select Sum12 as [Sum]

from #Sums

where Num1 = @Num1

and Num2 = @Num2;

The table can be extended pretty much indefinitely, of course.

- 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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Heh. At least someone remembers their 3rd grade Addition tables! 🙂

[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]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

GSquared (4/17/2009)
Michael Valentine Jones (4/17/2009)
OK, as long as we are doing useless SQL, I want to add numbers together without using any Arithmetic, Logical, Assignment, String, Bitwise, or Unary Operators or any Mathematical or Aggregate Functions.
create table #Sums (
Num1 int not null,
Num2 int not null,
Sum12 int);
insert into #Sums (Num1, Num2, Sum12)
select 0, 0, 0 union all
select 1, 0, 1 union all
select 2, 0, 2 union all
select 3, 0, 3 union all
select 4, 0, 4 union all
select 5, 0, 5 union all
select 6, 0, 6 union all
select 7, 0, 7 union all
select 8, 0, 8 union all
select 9, 0, 9 union all
select 0, 1, 1 union all
select 1, 1, 2 union all
select 2, 1, 3 union all
select 3, 1, 4 union all
select 4, 1, 5 union all
select 5, 1, 6 union all
select 6, 1, 7 union all
select 7, 1, 8 union all
select 8, 1, 9 union all
select 9, 1, 10 union all
select 0, 2, 2 union all
select 1, 2, 3 union all
select 2, 2, 4 union all
select 3, 2, 5 union all
select 4, 2, 6 union all
select 5, 2, 7 union all
select 6, 2, 8 union all
select 7, 2, 9 union all
select 8, 2, 10 union all
select 9, 2, 11 union all
select 0, 3, 3 union all
select 1, 3, 4 union all
select 2, 3, 5 union all
select 3, 3, 6 union all
select 4, 3, 7 union all
select 5, 3, 8 union all
select 6, 3, 9 union all
select 7, 3, 10 union all
select 8, 3, 11 union all
select 9, 3, 12 union all
select 0, 4, 4 union all
select 1, 4, 5 union all
select 2, 4, 6 union all
select 3, 4, 7 union all
select 4, 4, 8 union all
select 5, 4, 9 union all
select 6, 4, 10 union all
select 7, 4, 11 union all
select 8, 4, 12 union all
select 9, 4, 13 union all
select 0, 5, 5 union all
select 1, 5, 6 union all
select 2, 5, 7 union all
select 3, 5, 8 union all
select 4, 5, 9 union all
select 5, 5, 10 union all
select 6, 5, 11 union all
select 7, 5, 12 union all
select 8, 5, 13 union all
select 9, 5, 14 union all
select 0, 6, 6 union all
select 1, 6, 7 union all
select 2, 6, 8 union all
select 3, 6, 9 union all
select 4, 6, 10 union all
select 5, 6, 11 union all
select 6, 6, 12 union all
select 7, 6, 13 union all
select 8, 6, 14 union all
select 9, 6, 15 union all
select 0, 7, 7 union all
select 1, 7, 8 union all
select 2, 7, 9 union all
select 3, 7, 10 union all
select 4, 7, 11 union all
select 5, 7, 12 union all
select 6, 7, 13 union all
select 7, 7, 14 union all
select 8, 7, 15 union all
select 9, 7, 16 union all
select 0, 8, 8 union all
select 1, 8, 9 union all
select 2, 8, 10 union all
select 3, 8, 11 union all
select 4, 8, 12 union all
select 5, 8, 13 union all
select 6, 8, 14 union all
select 7, 8, 15 union all
select 8, 8, 16 union all
select 9, 8, 17 union all
select 0, 9, 9 union all
select 1, 9, 10 union all
select 2, 9, 11 union all
select 3, 9, 12 union all
select 4, 9, 13 union all
select 5, 9, 14 union all
select 6, 9, 15 union all
select 7, 9, 16 union all
select 8, 9, 17 union all
select 9, 9, 18;
declare @Num1 int, @Num2 int;
select @Num1 = 1, @Num2 = 5;
select Sum12 as [Sum]
from #Sums
where Num1 = @Num1
and Num2 = @Num2;
The table can be extended pretty much indefinitely, of course.

I did say no Assignment operators, so that disqualifies this statement:

select @Num1 = 1, @Num2 = 5;

GSquared SSC Guru Points: 260824 More actions · Answer 9

That's not part of the addition. That's just setting up a framework to run it. Run the query without that, with the numbers directly in the Where clause, instead of using variables, you'll be fine.

If you truly want addition without any mathematical, logical, etc., arguments, here you go:

Question: "What's some number added to another number?" (Can't have assignments in there, so you can't actually state what numbers you want to add.)

Answer: "A number."

It's a correct answer! Woot! 🙂

- 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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

GSquared (4/20/2009)
That's not part of the addition. That's just setting up a framework to run it. Run the query without that, with the numbers directly in the Where clause, instead of using variables, you'll be fine.
If you truly want addition without any mathematical, logical, etc., arguments, here you go:
Question: "What's some number added to another number?" (Can't have assignments in there, so you can't actually state what numbers you want to add.)
Answer: "A number."
It's a correct answer! Woot! 🙂

I was gonna suggest sp_Abacus, but your solution runs faster.

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

abhi620 SSC Enthusiast Points: 126 More actions · Answer 11

hi,

it is batch code it gives revers of all rows in the table........

i think this is good solution for --reversing data with out using REVERSE() function

create table std

(id int identity,

name varchar(20))

insert into std(name)

values ('abhijeet'),('omkar'),('tushar')

create table #table

(id int ,

name varchar(20))

declare @STR varchar(20)

--if exists(select * from std)

--begin

declare @count int

set @count=(select COUNT(*) from std)

--declare @tempcntid int=@count

declare@i int=@count

--while exists(select * from std)

while @i!=0

begin

set @STR=(select name from std where id=@i)

declare@str2 varchar(20)=' '

declare @j-2 int=1

while @j-2<=LEN(@str)

begin

set @str2=SUBSTRING(@str,@j,1)+ @str2

set @j-2=@j+1

end

--print @str2

insert into #table(id,name)values(@i,@str2)

--set @tempcntid=@tempcntid-1

set @i=@i-1

end

go

select * from std

go

select * from #table

go

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 12

abhi620 (5/20/2011)
i think this is good solution for...

With only very few exceptions, the RBAR of a WHILE loop is almost never a good solution. Please try it on a couple of hundred thousand rows and see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James_R_Alves SSC Veteran Points: 259 More actions · Answer 13

Just modify the next iteration call a bit, and the recursive solution works:

ALTER function dbo.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 =

(

RIGHT(@instring,1) -- Could use SUBSTRING, but why not RIGHT ?

+

dbo.StringReverse(substring(@InString, 1, len(@InString)-1))

)

return @RevString

end

Obviously, "WITHOUT using any built-in functions" isn't quite accurate, as SUBSTRING and LEN are used, So if there are exceptions, RIGHT could also be used to provide better clarity...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Here's another method:

-- Parameters

DECLARE @String VARCHAR(8000)

SET @String = 'Reverse'

-- Local Variables

DECLARE @OutputString VARCHAR(8000); SET @OutputString = '';

DECLARE @StrLen INT; SET @StrLen = DATALENGTH(@String);

DECLARE @MappingTable TABLE (n INT PRIMARY KEY CLUSTERED);

INSERT INTO @MappingTable (n)

SELECT TOP(@StrLen) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM syscolumns a, syscolumns b;

UPDATE @MappingTable SET @OutputString = @OutputString + SUBSTRING(@String,(@StrLen+1)-n,1);

SELECT FormattedAmount = @OutputString;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sivajaggu11 SSC Enthusiast Points: 158 More actions · Answer 15

declare @string varchar(60)

set @string='reverse string'

;with Reverse_string

as

(

select @string string,cast('' as varchar(50)) Rstring,LEN(@string)LN

union all

select SUBSTRING(string,0,ln)string,cast(Rstring+substring(string,LN,1)as varchar(50)) Rstring,ln-1 ln from Reverse_string

where LN>0

)

select Rstring from Reverse_string where ln=0