Using a SEQUENCE object to generate letters.

Download PDF

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.

Tagged with: , , , , , , ,

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.