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 4:57 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, March 22, 2016 5:42 AM Points: 298, Visits: 797
 Hi, How will i reverse the number in SQL Server with out using reverse()
Post #1339728
 Posted Friday, August 3, 2012 5:04 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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
Post #1339732
 Posted Friday, August 3, 2012 5:24 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, November 29, 2016 4:40 AM Points: 2,492, Visits: 8,429
 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
Post #1339743
 Posted Friday, August 3, 2012 5:27 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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
Post #1339744
 Posted Friday, August 3, 2012 6:31 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 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
Post #1339776
 Posted Friday, August 3, 2012 7:32 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, March 22, 2016 5:42 AM Points: 298, Visits: 797
 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.
Post #1339823
 Posted Friday, August 3, 2012 7:33 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 1:35 PM Points: 2,063, Visits: 3,384
 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`
Post #1339824
 Posted Friday, August 3, 2012 7:45 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, September 16, 2016 12:22 PM Points: 3,849, Visits: 72,507
 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
Post #1339833
 Posted Friday, August 3, 2012 7:46 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 7:14 PM Points: 8,200, Visits: 17,740
 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