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
  SELECT Number + 1
  FROM Numbers
  WHERE Number + 1 <= 255
SELECT Number, CHAR(Number) AS Character FROM numbers

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     ÿ



2 Responses

Leave a Reply

The NuCaptcha API requires the PHP mcrypt module.