Blog

SQL Alphabet Table: Get Next Character

While writing some dynamic SQL to cycle through a set of Active Directory LDAP queries (don't ask, ugh) I needed to dynamically create a table with the values 0-9 and A-Z in it.  After a few minutes, okay maybe 30, over work I came up with the following T-SQL script.  It's been tested on MS SQL Server 2008 R2.  It should work on SQL Server Server 2000 and 2005 as well with minor mods.  Give it a shot and let me know.

 

--create a table to store the values DECLARE @Characters TABLE ( ID INT IDENTITY(1,1), Characters NCHAR(1) PRIMARY KEY )

--insert numbers 0-9 DECLARE @num INT= 0 WHILE @num <= 9 BEGIN

INSERT  @Characters ( Characters ) SELECT  @num

SELECT  @num = @num + 1

END

--add letters A-Z  (aka ASCII 65-90) DECLARE @asciiCode INT= 65 WHILE @asciiCode <= 90 BEGIN

INSERT  @Characters ( Characters ) SELECT  CHAR(@asciiCode)

SELECT  @asciiCode = @asciiCode + 1

END

--Be awesome!

SELECT * FROM @Characters AS c

No comments yet.

Leave a Reply