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)');

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

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

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)),'<numc>',''),'</numc>','')

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;

N-----------12345678910111213141516171819

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;

N----005

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;

-------------------------<N>0</N><N>0</N><N>5</N>

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)');

-------------------005