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
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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.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
;
That produces: -
N
----
0
0
5
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.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;
That produces: -
-------------------------
<N>0</N><N>0</N><N>5</N>
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