I am working on a chapter for Syngress' 70-433 test prep guide, and talking about character sets. In my research I wanted to write a query that would return the values 0 through 255 and the the character associated with each of those numbers.
Yeah if I had a table Like AdventureWorks.Person.Contact where there was a set of 256 contiguous values, I could highjack those values for my source numbers, but that seems much less elegant.
Here is what I came up with:
| WITH numbers AS
(
SELECT 0 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number + 1 <= 255
)
SELECT Number, CHAR(Number) AS Character FROM numbers
OPTION (MAXRECURSION 255); |
Note the OPTION (MAXRECURSION 255) at the end. Without that it will error after the first 100 rows (really its after the 100th recursion along this branch, but I digress) by default. But that option will cause it to error if I don't stop the recursion myself at 255, hence the WHERE Number + 1 <= 255 where clause on the recursive query definition in the CTE.
Anyhow, if you run the query above you will get a result similar to the following
| Number Character
------ ---------
... ...
63 ?
64 @
65 A
66 B
67 C
... ...
97 a
98 b
99 c
... ...
253 ý
254 þ
255 ÿ |
Enjoy!
Posts
