Using a SEQUENCE object to generate letters.

SQL Server 2012 introduces a new T-SQL object used to generate numbers, a SEQUENCE, similar to an identity column. You can think of a sequence as a take a number object, you just ask it to give you a number, and you get a number.

When creating a SEQUENCE you need to specify the following:

Start
Increment
Min Value, Max Value
Cycle / No Cycle – starts of when the Max value is hit or not
Cache

The SEQUENCE is all about generating numbers…

The question comes up on using a SEQUENCE to generate letters, to rotate through a SEQUENCE of A to Z, then to start over?

Technically a SEQUENCE can only generate numbers, but here is how you can use it to generate letters.

Here is the sample code to do it.


CREATE SEQUENCE lettersSequence
 AS tinyint
 MINVALUE 65
 MAXVALUE 90
 CYCLE
 INCREMENT BY 1 ;

SELECT char(NEXT VALUE FOR lettersSequence);

SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);

Enjoy.

Using a SEQUENCE object to generate letters.

Leave a Reply Cancel reply