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 03, 2012 9:06 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 2:37 PM Points: 1,194, Visits: 2,031
 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 03, 2012 9:25 AM
 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 = 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 2Jeremy Oursler
Post #1339922
 Posted Friday, August 03, 2012 9:38 AM
 Hall 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 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 03, 2012 9:41 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 4:27 AM Points: 1,592, Visits: 6,523
 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 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 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;` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never 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 Group: General Forum Members Last Login: Today @ 6:20 AM Points: 2,768, Visits: 5,934
 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.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum 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 Group: General Forum Members Last Login: Today @ 6:23 AM Points: 7,087, Visits: 14,700
 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 03, 2012 3:24 PM
 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= 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 2Jeremy Oursler
Post #1340128
 Posted Saturday, August 04, 2012 3:11 AM
 Ten 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 = 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: Today @ 5:51 AM Points: 3,596, Visits: 5,111
 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

 Permissions