SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reverse Of Number without Using reverse()


Reverse Of Number without Using reverse()

Author
Message
Shadab Shah
Shadab Shah
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 798
Hi,
How will i reverse the number in SQL Server with out using reverse()
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16362 Visits: 19554
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
Exploring Recursive CTEs by Example Dwain Camps
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3910 Visits: 8472
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16362 Visits: 19554
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
Exploring Recursive CTEs by Example Dwain Camps
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23671 Visits: 9730
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
Shadab Shah
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 798
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
sestell1
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2549 Visits: 3463
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
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4832 Visits: 72518
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
For tips on how to post your problems
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16663 Visits: 19104
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
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3910 Visits: 8472
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
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search