Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Reverse Of Number without Using reverse() Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, August 3, 2012 9:06 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 2:35 PM Points: 2,065, Visits: 3,385
 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.90DECLARE @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 3, 2012 9:25 AM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, November 22, 2016 1:08 AM Points: 887, Visits: 1,787
 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 for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail ShawNeed to Split some strings? Jeff Moden's DelimitedSplit8KJeff Moden's Cross tab and Pivots Part 1Jeff Moden's Cross tab and Pivots Part 2
Post #1339922
 Posted Friday, August 3, 2012 9:38 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, September 16, 2016 12:22 PM Points: 3,849, Visits: 72,507
 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 DBAProud member of the Anti-RBAR alliance.For help with Performance click this linkFor tips on how to post your problems
Post #1339929
 Posted Friday, August 3, 2012 9:41 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 11:11 AM Points: 1,643, Visits: 6,987
 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 3, 2012 9:54 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 8:33 AM Points: 2,015, Visits: 22,627
 Here's another, purely numeric, doesn't use SUBSTRINGs`DECLARE @num INT = 102948092;WITH Tens(Pos,Val) AS (SELECT 1, 1 UNION ALLSELECT 2, 10 UNION ALLSELECT 3, 100 UNION ALLSELECT 4, 1000 UNION ALLSELECT 5, 10000 UNION ALLSELECT 6, 100000 UNION ALLSELECT 7, 1000000 UNION ALLSELECT 8, 10000000 UNION ALLSELECT 9, 100000000 UNION ALLSELECT 10,1000000000),Digits AS (SELECT Pos, COUNT(*) OVER() AS Total, (@num / Val) % 10 AS DigitFROM TensWHERE Val<=@num)SELECT @num AS Src, ISNULL(SUM(t.Val*d.Digit),0) AS RevFROM Digits dINNER JOIN Tens t ON t.Pos=d.Total-d.Pos+1;` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
Post #1339944
 Posted Friday, August 3, 2012 9:59 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 8:14 PM Points: 8,228, Visits: 17,799
 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 '' ENDFROM( VALUES (-521), (-67342), (-14), (4), (2111222333), (0), (4000)) SampleData(N)` 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
Post #1339949
 Posted Friday, August 3, 2012 10:32 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:19 AM Points: 7,507, Visits: 17,959
 Yet another in a cast of many:`DECLARE @num INT set @num= 102948092DECLARE @numstr VARCHAR(100)SET @numstr = @numDECLARE @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 3, 2012 3:24 PM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, November 22, 2016 1:08 AM Points: 887, Visits: 1,787
 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= 102995348548092DECLARE @numstr VARCHAR(100)SET @numstr = @numDECLARE @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 DESCSELECT @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 ShawNeed to Split some strings? Jeff Moden's DelimitedSplit8KJeff Moden's Cross tab and Pivots Part 1Jeff Moden's Cross tab and Pivots Part 2
Post #1340128
 Posted Saturday, August 4, 2012 3:11 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, February 18, 2016 6:33 AM Points: 1,127, Visits: 1,616
 How about this Cadavre???.....`Declare @num bigint Set @num = 12345678Select @num As Number, Stuff((select Left(Right(Cast(@num As Varchar(10)), n), 1) From TallyWhere n <= Len(Cast(@num As Varchar(10))) For XML Path('')), 1, 1, Right(Cast(@num As Varchar(10)), 1)) As ReverseOfNumber` Vinu VijayanFor 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1356580

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.