Search code examples
sqlsql-serversql-server-2008charauto-increment

How to enable auto-increment in letters(A, B, C, D...) in SQL SERVER 2008?

I am new for SQL SERVER. I found how to auto increment numbers for column.

enter image description here

CREATE TABLE Region
(
RegionId int IDENTITY(1,1),
RegionName varchar(50),
);

Question: How to enable "auto increment" in letters(A, B, C, D...) like this?

enter image description here

Solution

  • Whilst as Damien rightly says in the comments there might be gaps in the values and that it's not a good idea to rely on the values being contiguous, how about adding RegionName as a calculated column:

    ALTER TABLE Region
ADD RegionName AS CHAR(RegionID + 64)

    This works because the ASCII value of 'A' is 65 - so assuming your identity column starts at value 1 with increments of 1, you should get CHAR (64+1) = CHAR(65) - the code for A, 2 = CHAR(66) = B and so on.

    Of course this only works based on the assumptions that you don't want to change RegionName to something friendlier later (as the column is calculated), and that you'll only have a small range of values - for example, what would you expect to happen if there are more than 26 regions? With my idea you'll start to get some funky results after that - symbols, then lower case letters etc. see an ASCII table if you're unfamiliar with the idea. If you want something cleverer like it to start creating regions with the name AA, AB, AC etc. I'd suggest following the excellent sequence generating link posted by Aishvarya in the comments.

    SQL Fiddle example