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 03, 2012 9:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 1,192, Visits: 2,024
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)');

Post #1339908
Posted Friday, August 03, 2012 9:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
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 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

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

Jeremy Oursler
Post #1339922
Posted Friday, August 03, 2012 9:38 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: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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
For tips on how to post your problems
Post #1339929
Posted Friday, August 03, 2012 9:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:10 AM
Points: 1,592, Visits: 6,519
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)

Post #1339934
Posted Friday, August 03, 2012 9:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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;



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1339944
Posted Friday, August 03, 2012 9:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 2,763, Visits: 5,915
Peter Brinkhaus (8/3/2012)
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)



If you want it to work for N<0
SELECT
N,
LEFT(CAST(ABS(N % 10) AS CHAR(1)) +
CAST(ABS((N / 10) % 10) AS CHAR(1)) +
CAST(ABS((N / 100) % 10) AS CHAR(1)) +
CAST(ABS((N / 1000) % 10) AS CHAR(1)) +
CAST(ABS((N / 10000) % 10) AS CHAR(1)) +
CAST(ABS((N / 100000) % 10) AS CHAR(1)) +
CAST(ABS((N / 1000000) % 10) AS CHAR(1)) +
CAST(ABS((N / 10000000) % 10) AS CHAR(1)) +
CAST(ABS((N / 100000000) % 10) AS CHAR(1)) +
CAST(ABS((N / 1000000000) % 10) AS CHAR(1)),
CASE WHEN N < 0 THEN LEN(CAST(N AS VARCHAR(10))) - 1 ELSE LEN(CAST(N AS VARCHAR(10))) END) +
CASE WHEN N < 0 THEN '-' ELSE '' END
FROM
(
VALUES (-521), (-67342), (-14), (4), (2111222333), (0), (4000)
) SampleData(N)





Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339949
Posted Friday, August 03, 2012 10:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
Yet another in a cast of many:

DECLARE @num INT 
set @num= 102948092

DECLARE @numstr VARCHAR(100)
SET @numstr = @num

DECLARE @revstr VARCHAR(100)
set @revstr=replicate(' ',len(@numstr));

;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)
select @revstr=stuff(@revstr,len(@numstr)-n+1,1,substring(@numstr,n,1)) from tally where n<=len(@numstr)



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1339973
Posted Friday, August 03, 2012 3:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
Here is one with a rCTE. to put it back together you can use FOR XML PATH or SELECT @Var = @Var + part. i have done one with FOR XML PATH so figured id post one with SELECT @Var = @Var + part

DECLARE @num BIGINT 
set @num= 102995348548092

DECLARE @numstr VARCHAR(100)
SET @numstr = @num

DECLARE @Str VARCHAR(100) = ''

;WITH rCTE(ID,Ch) AS (SELECT 1, SUBSTRING(@numstr,1,1)
UNION ALL
SELECT ID + 1, SUBSTRING(@numstr,ID+1,1)
FROM rCTE
WHERE ID < DATALENGTH(@numstr))
SELECT @Str = @Str + Ch FROM rCTE ORDER BY ID DESC
SELECT @Str




For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden 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

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

Jeremy Oursler
Post #1340128
Posted Saturday, August 04, 2012 3:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
How about this Cadavre???.....

Declare @num bigint 
Set @num = 12345678
Select @num As Number, Stuff((select Left(Right(Cast(@num As Varchar(10)), n), 1) From Tally
Where n <= Len(Cast(@num As Varchar(10))) For XML Path('')), 1, 1, Right(Cast(@num As Varchar(10)), 1)) As ReverseOfNumber



Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1340174
Posted Monday, September 10, 2012 1:22 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: Today @ 9:05 PM
Points: 3,594, Visits: 5,104
Better late than never?

DECLARE @num BIGINT = 1234567890

;WITH Tally (n) AS (
SELECT TOP (LEN(@num)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
SELECT (SELECT SUBSTRING(RTRIM(@Num), 1+LEN(@Num)-n, 1) FROM Tally FOR XML PATH(''))





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1356580
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse