## Reverse Of Number without Using reverse()

 Author Message Shadab Shah SSCrazy Group: General Forum Members Points: 2321 Visits: 798 Hi, How will i reverse the number in SQL Server with out using reverse() ChrisM@Work SSC Guru Group: General Forum Members Points: 97322 Visits: 20698 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 ModenExploring Recursive CTEs by Example Dwain Camps Cadavre SSC-Insane Group: General Forum Members Points: 22100 Visits: 8519 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 learnFor 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/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockCraig Wilkinson - Software EngineerLinkedIn ChrisM@Work SSC Guru Group: General Forum Members Points: 97322 Visits: 20698 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 ModenExploring Recursive CTEs by Example Dwain Camps GSquared SSC Guru Group: General Forum Members Points: 139861 Visits: 9731 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon Shadab Shah SSCrazy Group: General Forum Members Points: 2321 Visits: 798 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. sestell1 SSCertifiable Group: General Forum Members Points: 6070 Visits: 3688 How is this for a novel approach?`DECLARE @Number FloatSET @Number = 1234.56-- Create a string version of the numberDECLARE @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_NumberFROM ( 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` mtassin SSChampion Group: General Forum Members Points: 13320 Visits: 72528 My turn`DECLARE @num INT = 102948092DECLARE @numstr VARCHAR(10)SET @numstr = @numDECLARE @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 @tabSELECTN,SUBSTRING(@numstr,N,1)FROM tallyWHEREN <= LEN(@numstr)SELECT@num,newstr = REPLACE(REPLACE(CAST((SELECT numc FROM @tab ORDER BY num desc FOR XML PATH('')) AS varchar(MAX)),'',''),'','')`Edit: wanted to use XML PATH but forgot to. --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 Luis Cazares SSC Guru Group: General Forum Members Points: 96016 Visits: 21205 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' 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 Cadavre SSC-Insane Group: General Forum Members Points: 22100 Visits: 8519 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-----------12345678910111213141516171819`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.NFROM (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----005`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.NFROM (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 queryFOR XML PATH(''), TYPE;`That produces: -`-------------------------005`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 learnFor 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/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockCraig Wilkinson - Software EngineerLinkedIn