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