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 Of Number without Using reverse() Expand / Collapse
Author
Message
Posted Friday, August 3, 2012 4:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:05 AM
Points: 255, Visits: 620
Hi,
How will i reverse the number in SQL Server with out using reverse()
Post #1339728
Posted Friday, August 3, 2012 5:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 7,181, Visits: 13,634
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
Post #1339732
Posted Friday, August 3, 2012 5:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1339743
Posted Friday, August 3, 2012 5:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 7,181, Visits: 13,634
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
Post #1339744
Posted Friday, August 3, 2012 6:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1339776
Posted Friday, August 3, 2012 7:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:05 AM
Points: 255, Visits: 620
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.
Post #1339823
Posted Friday, August 3, 2012 7:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 1,301, Visits: 2,254
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

Post #1339824
Posted Friday, August 3, 2012 7:45 AM


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: Friday, August 15, 2014 8:38 AM
Points: 3,668, Visits: 72,432
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
Post #1339833
Posted Friday, August 3, 2012 7:46 AM


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 @ 5:19 PM
Points: 3,499, Visits: 7,549
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339834
Posted Friday, August 3, 2012 7:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1339837
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse