Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Programming » General » Reverse string without built in functions
Add to briefcase
29 posts, Page 3 of 3
«««123

Reverse string without built in functions Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
GSquared
Posted Friday, April 17, 2009 1:32 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571
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
Post #699735
RBarryYoung
Posted Friday, April 17, 2009 3:09 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
Heh. At least someone remembers their 3rd grade Addition tables!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #699809
Michael Valentine Jones
Posted Friday, April 17, 2009 8:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517
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;
Post #699925
GSquared
Posted Monday, April 20, 2009 7:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571
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
Post #700627
jcrawf02
Posted Monday, April 20, 2009 8:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 2,551, Visits: 18,885
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #700678
abhi620
Posted Friday, May 20, 2011 10:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:55 AM
Points: 2, Visits: 77
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 int=1

while @j<=LEN(@str)
begin
set @str2=SUBSTRING(@str,@j,1)+ @str2
set @j=@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
Post #1112843
Jeff Moden
Posted Saturday, May 21, 2011 10:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1112955
James_R_Alves
Posted Thursday, January 24, 2013 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:50 PM
Points: 11, Visits: 42
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...
Post #1411162
ChrisM@Work
Posted Thursday, January 24, 2013 8:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1411182
« Prev Topic | Next Topic »

Add to briefcase
29 posts, Page 3 of 3
«««123

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.