## Reverse Of Number without Using reverse()

 Author Message sestell1 Hall of Fame Group: General Forum Members Points: 3558 Visits: 3511 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)');` CapnHector Hall of Fame Group: General Forum Members Points: 3333 Visits: 1789 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 mtassin SSCertifiable Group: General Forum Members Points: 7332 Visits: 72521 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 Peter Brinkhaus Hall of Fame Group: General Forum Members Points: 3161 Visits: 7404 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)` Mark Cowne SSCertifiable Group: General Forum Members Points: 6543 Visits: 25590 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 Luis Cazares SSC-Forever Group: General Forum Members Points: 42536 Visits: 19838 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 Matt Miller (4) One Orange Chip Group: General Forum Members Points: 29935 Visits: 19009 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? CapnHector Hall of Fame Group: General Forum Members Points: 3333 Visits: 1789 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 vinu512 Hall of Fame Group: General Forum Members Points: 3707 Visits: 1626 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`:-P Vinu VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-) dwain.c SSCoach Group: General Forum Members Points: 18113 Visits: 6431 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