Hints and Tips One-liners SQL

List of Alphabetic Values in SQL Server

I really like this use of the undocumented but widely used master.dbo.spt_values table to give a list of characters e.g. a-z or A-Z:

SELECT CHAR(NUMBER) FROM master.dbo.spt_values
WHERE NUMBER BETWEEN 97 AND 122 --- Lowercase a-z 
--WHERE number between 65 and 90 --- Uppercase A-Z

Other ranges such as “number between 48 and 57” are also useful (that gives 0 through 9).

There are of course other techniques to address this requirement such as creating a permanent table of the numbers/letters required. I’m not advocating the above over others – I just like the “neatness” of the above approach. Perhaps using sys.all_objects or some other documented table/view would be safer – although I doubt microsoft will remove master.dbo.spt_values any time soon. Here’s another approach you might prefer:

CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+64) AS upper_case,
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+96) AS lower_case
FROM sys.all_objects

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.