Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Reverse string without built in functions Expand / Collapse
Author
Message
Posted Friday, April 17, 2009 1:32 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
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: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
Posted Friday, April 17, 2009 8:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:37 PM
Points: 3,116, Visits: 11,385
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
Posted Monday, April 20, 2009 7:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Monday, April 20, 2009 8:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 2,656, Visits: 19,181
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
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: Thursday, March 27, 2014 5:41 AM
Points: 2, Visits: 101
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
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: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1112955
Posted Thursday, January 24, 2013 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:02 AM
Points: 21, Visits: 73
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
Posted Thursday, January 24, 2013 8:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 7,107, Visits: 13,462
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 «««123

Permissions Expand / Collapse