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)')
For 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 Moden