Using a Recursive CTE to generate a contiguous sequence of values...

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!