Reverse Of Number without Using reverse()

Question

Post reply

Reverse Of Number without Using reverse()

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 3, 2012 at 4:57 am

#260076

Hi,
How will i reverse the number in SQL Server with out using reverse()

Viewing 15 posts - 1 through 15 (of 24 total)

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

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

shahsn11 (8/3/2012)
Hi,
How will i reverse the number in SQL Server with out using reverse()

Why don't you want to use reverse() ?

^{“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

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

shahsn11 (8/3/2012)
Hi,
How will i reverse the number in SQL Server with out using reverse()

Is this an intellectual challenge? Because otherwise, use reverse().

I guess you could do something like this for INT: -

DECLARE @number INT = 500;

SELECT (SELECT N

FROM (SELECT TOP (LEN(@number))

SUBSTRING(CAST(@number AS VARCHAR(19)),N,1)

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

(10),(11),(12),(13),(14),(15),(16),(17),

(18),(19))a(N)

WHERE N <= (LEN(@number))

ORDER BY N DESC) b(N)

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(19)');

Returns : - 005 for 500.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Cadavre (8/3/2012)
shahsn11 (8/3/2012)
Hi,
How will i reverse the number in SQL Server with out using reverse()
Is this an intellectual challenge? Because otherwise, use reverse().
I guess you could do something like this for INT: -
DECLARE @number INT = 500;
SELECT (SELECT N
FROM (SELECT TOP (LEN(@number))
SUBSTRING(CAST(@number AS VARCHAR(19)),N,1)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),
(18),(19))a(N)
WHERE N <= (LEN(@number))
ORDER BY N DESC) b(N)
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(19)');
Returns : - 005 for 500.

Heh...

SELECT Num = (

SELECT SUBSTRING(x.NumAsString,tally.n,1)

FROM (SELECT 123456789012335.782) MyValue (mynumber)

CROSS APPLY (SELECT CAST(MyValue.mynumber AS VARCHAR(19))) x (NumAsString)

CROSS APPLY(

SELECT TOP (DATALENGTH(x.NumAsString)) n

FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL

SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL

SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL

SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19) t (n)

) tally (n)

ORDER BY n DESC

FOR XML PATH(''),type).value('.','varchar(max)')

^{“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

GSquared SSC Guru Points: 260824 More actions · Answer 4

A guy I know recently had something similar in an interview. The interviewer wanted him to test if a word was a pallindrome without using a reverse function. Wasn't T-SQL though.

He came up with a solution in the interview.

Afterward, he asked friends (including me) how to do it more efficiently. Was a fun challenge.

(Edit to clarify.)

- 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

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 5

I really appreciate you for your answer thanks. And the output was the same as i was thinking.

Since i am a newbie , i am having some problem while trying to understand your code.

Following are the area which are new to me i would really appreciate if you could spare some time and help me with them. Either you can provide some link or you can explain them.

SELECT (SELECT N

FROM (SELECT TOP (LEN(@number))

SUBSTRING(CAST(@number AS VARCHAR(19)),N,1)

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

(10),(11),(12),(13),(14),(15),(16),(17),

(18),(19))a(N)

WHERE N <= (LEN(@number))

ORDER BY N DESC) b(N)

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(19)');

In the above example we have not define the variable 'a' , 'b' & 'N' and then also we are using it.

Please help me out with the above question.

sestell1 SSChampion Points: 10230 More actions · Answer 6

How is this for a novel approach?

DECLARE @Number Float

SET @Number = 1234.56

-- Create a string version of the number

DECLARE @Number_String VarChar(250)

SET @Number_String = CONVERT(VarChar(250), @Number);

-- Create a "Common Table Expression" with numbers 0-9 as rows.

WITH Base (Digit) AS

(

SELECT 0 UNION ALL

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9

)

-- Re-assemble the characters in the reverse order.

SELECT

IsNull(MAX(CASE Backwards.Position WHEN 10 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 9 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 8 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 7 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 6 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 5 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 4 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 3 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 2 THEN Backwards.Number ELSE Null END),'') +

IsNull(MAX(CASE Backwards.Position WHEN 1 THEN Backwards.Number ELSE Null END),'') AS Reverse_Number

FROM

(

SELECT

-- Select a substring from the current number character position

SUBSTRING(@Number_String, All_Numbers.Number, 1) AS Number,

-- The position the substring started at...

All_Numbers.Number AS Position

FROM

(

-- Create a table query with numbers 1-100,000 as rows

-- by joining the Base table once for each decimal place...

SELECT

D5.Digit * 10000 +

D4.Digit * 1000 +

D3.Digit * 100 +

D2.Digit * 10 +

D1.Digit + 1 AS Number

FROM

Base D1 CROSS JOIN

Base D2 CROSS JOIN

Base D3 CROSS JOIN

Base D4 CROSS JOIN

Base D5

) All_Numbers

WHERE

-- Filter off any numbers greater than the length of the string.

All_Numbers.Number <= LEN(@Number_String)

) Backwards

mtassin SSC-Insane Points: 23099 More actions · Answer 7

My turn

DECLARE @num INT = 102948092

DECLARE @numstr VARCHAR(10)

SET @numstr = @num

DECLARE @tab TABLE(

num INT,

numc CHAR(1))

;WITH

t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

t5 AS (SELECT 1 N FROM t4 x, t4 y),

Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N

FROM t3 x, t3 y)

INSERT @tab

SELECT

N,

SUBSTRING(@numstr,N,1)

FROM

tally

WHERE

N <= LEN(@numstr)

SELECT

@num,

newstr = REPLACE(REPLACE(CAST((SELECT numc FROM @tab ORDER BY num desc FOR XML PATH('')) AS varchar(MAX)),'<numc>',''),'</numc>','')

Edit: wanted to use XML PATH but forgot to.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

shahsn11 (8/3/2012)
I really appreciate you for your answer thanks. And the output was the same as i was thinking.
Since i am a newbie , i am having some problem while trying to understand your code.
Following are the area which are new to me i would really appreciate if you could spare some time and help me with them. Either you can provide some link or you can explain them.
SELECT (SELECT N
FROM (SELECT TOP (LEN(@number))
SUBSTRING(CAST(@number AS VARCHAR(19)),N,1)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),
(18),(19))a(N)
WHERE N <= (LEN(@number))
ORDER BY N DESC) b(N)
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(19)');
In the above example we have not define the variable 'a' , 'b' & 'N' and then also we are using it.
Please help me out with the above question.

'a' , 'b' & 'N' aren't variables.

'a' & 'b' are alias for tables

'N' is an alias for the unique field of 'a' & 'b'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

shahsn11 (8/3/2012)
I really appreciate you for your answer thanks. And the output was the same as i was thinking.
Since i am a newbie , i am having some problem while trying to understand your code.
Following are the area which are new to me i would really appreciate if you could spare some time and help me with them. Either you can provide some link or you can explain them.
SELECT (SELECT N
FROM (SELECT TOP (LEN(@number))
SUBSTRING(CAST(@number AS VARCHAR(19)),N,1)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),
(18),(19))a(N)
WHERE N <= (LEN(@number))
ORDER BY N DESC) b(N)
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(19)');
In the above example we have not define the variable 'a' , 'b' & 'N' and then also we are using it.
Please help me out with the above question.

There are no variables 'a', 'b' or 'N' in my code. The only variable I used was the INT @number.

The 'a' and 'b' are table alias'. The N is a column name.

See BOL about the table value constructor, which I suspect is what confused you.

e.g.

SELECT *

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

(10),(11),(12),(13),(14),(15),(16),(17),

(18),(19)

)a --Alias for the values constructor

(N) --Column name for the data in the values constructor

;

Returns: -

N

-----------

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

So just a list of numbers. This is to make it so we don't have to loop over the number that we want to reverse.

Next: -

DECLARE @number INT = 500;

SELECT b.N

FROM (SELECT TOP (LEN(@number))

SUBSTRING(CAST(@number AS VARCHAR(19)),a.N,1)

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

(10),(11),(12),(13),(14),(15),(16),(17),

(18),(19)

)a --Alias for the values constructor

(N) --Column name for the data in the values constructor

WHERE a.N <= (LEN(@number))

ORDER BY a.N DESC

) b --Alias for the inner query

(N) --Column name for the data in the inner query

;

That produces: -

N

----

0

5

So we now have a data-set with 0,0 and 5. OK, so now we want to concatenate that back together so that we have a reversed string.

DECLARE @number INT = 500;

SELECT b.N

FROM (SELECT TOP (LEN(@number))

SUBSTRING(CAST(@number AS VARCHAR(19)),a.N,1)

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

(10),(11),(12),(13),(14),(15),(16),(17),

(18),(19)

)a --Alias for the values constructor

(N) --Column name for the data in the values constructor

WHERE a.N <= (LEN(@number))

ORDER BY a.N DESC

) b --Alias for the inner query

(N) --Column name for the data in the inner query

FOR XML PATH(''), TYPE;

That produces: -

-------------------------

<N>0</N><N>0</N><N>5</N>

Not quite right. Let's get rid of those tags by letting SQL Server know that this is a varchar.

DECLARE @number INT = 500;

SELECT (SELECT b.N

FROM (SELECT TOP (LEN(@number))

SUBSTRING(CAST(@number AS VARCHAR(19)),a.N,1)

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),

(10),(11),(12),(13),(14),(15),(16),(17),

(18),(19)

)a --Alias for the values constructor

(N) --Column name for the data in the values constructor

WHERE a.N <= (LEN(@number))

ORDER BY a.N DESC

) b --Alias for the inner query

(N) --Column name for the data in the inner query

FOR XML PATH(''), TYPE

).value('.', 'VARCHAR(19)');

That produces: -

-------------------

005

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sestell1 SSChampion Points: 10230 More actions · Answer 10

Interesting thread.

Combining my favorite parts from all replies I think I would now do something like this:

DECLARE @Number Decimal(10,2)

SET @Number = 12345678.90

DECLARE @Number_String VarChar(25)

SET @Number_String = Convert(VarChar(25), @Number)

;WITH

T1 AS (SELECT 1 X UNION ALL SELECT 1 X),

Sequence AS (SELECT Row_Number() OVER (ORDER BY A.X) AS Number FROM T1 A, T1 B, T1 C, T1 D)

SELECT

(

SELECT

SUBSTRING(@Number_String, Sequence.Number, 1)

FROM

Sequence

WHERE

Sequence.Number <= LEN(@Number_String)

ORDER BY

Sequence.Number DESC

FOR XML PATH(''), TYPE

)

.value('.', 'VARCHAR(25)');

CapnHector SSCoach Points: 16143 More actions · Answer 11

Well here is my solution. since we can order by when we take our substring we can just put the FOR XML PATH ('') in that query and not have to worry about any tags.

DECLARE @num INT = 102948092

DECLARE @NumString VARCHAR(10)

SET @NumString=@num

;WITH e1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) X(N)),

e2(N) AS (SELECT 1 FROM e1 a, e1 b),

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2)

SELECT SUBSTRING(@NumString,N,1)

FROM cteTally

WHERE N <= DATALENGTH(@NumString)

ORDER BY N DESC

FOR XML PATH ('')

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 12

capn.hector (8/3/2012)
Well here is my solution. since we can order by when we take our substring we can just put the FOR XML PATH ('') in that query and not have to worry about any tags.

Neat, I forgot that if you use a formula (the substring) that the column has no name and that FOR XML PATH will then just make a string... Nice.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

matzer SSCrazy Eights Points: 9727 More actions · Answer 13

sestell1 (8/3/2012)
Interesting thread.

Well, in that case, here's another one. Only works on INT's >= 0.

SELECT

N,

LEFT(CAST(N % 10 AS CHAR(1)) +

CAST((N / 10) % 10 AS CHAR(1)) +

CAST((N / 100) % 10 AS CHAR(1)) +

CAST((N / 1000) % 10 AS CHAR(1)) +

CAST((N / 10000) % 10 AS CHAR(1)) +

CAST((N / 100000) % 10 AS CHAR(1)) +

CAST((N / 1000000) % 10 AS CHAR(1)) +

CAST((N / 10000000) % 10 AS CHAR(1)) +

CAST((N / 100000000) % 10 AS CHAR(1)) +

CAST((N / 1000000000) % 10 AS CHAR(1)), LEN(CAST(N AS VARCHAR(10))))

FROM

(

VALUES (521), (67342), (14), (4), (2111222333), (0), (4000)

) SampleData(N)

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 14

Here's another, purely numeric, doesn't use SUBSTRINGs

DECLARE @num INT = 102948092;

WITH Tens(Pos,Val) AS (

SELECT 1, 1 UNION ALL

SELECT 2, 10 UNION ALL

SELECT 3, 100 UNION ALL

SELECT 4, 1000 UNION ALL

SELECT 5, 10000 UNION ALL

SELECT 6, 100000 UNION ALL

SELECT 7, 1000000 UNION ALL

SELECT 8, 10000000 UNION ALL

SELECT 9, 100000000 UNION ALL

SELECT 10,1000000000),

Digits AS (

SELECT Pos, COUNT(*) OVER() AS Total,

(@num / Val) % 10 AS Digit

FROM Tens

WHERE Val<=@num)

SELECT @num AS Src,

ISNULL(SUM(t.Val*d.Digit),0) AS Rev

FROM Digits d

INNER JOIN Tens t ON t.Pos=d.Total-d.Pos+1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537