Reverse Of Number without Using reverse()
-
Hi,
How will i reverse the number in SQL Server with out using reverse()
-
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 ShawFor 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 -
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/ -
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 ShawFor 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 -
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
-
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.
-
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
-
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] -
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'
-
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
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/ -
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)');
-
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 = 102948092DECLARE @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] -
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] -
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)
-
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply