7

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

Joe Richard
  • 1,520
  • 7
  • 20
  • 31
  • Whenever you use an `IDENTITY` column, you should not *care* or in any way *depend* upon the actual numeric values produced. It should be treated as an opaque blob that you happen to be able to store in a numeric column. As such, even your first part (generating a sequence of numbers) is incorrect, if you're in some way relying upon the sequence of numbers produced. – Damien_The_Unbeliever Dec 06 '13 at 11:34
  • 3
    This might help - http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server – MusicLovingIndianGirl Dec 06 '13 at 11:35
  • You make a sequence function yourself that does it for you, which you can call that will give you the next value. But frankly - I don't see why you'd want to "auto increment" a character like that? – Allan S. Hansen Dec 06 '13 at 11:36
  • @AllanS.Hansen, It is coursework task to give names for regions even if it is not meaningful for human – Joe Richard Dec 06 '13 at 11:39
  • @Aishvarya, Thank you for article. I will read it now – Joe Richard Dec 06 '13 at 11:41

1 Answers1

7

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

Bridge
  • 29,818
  • 9
  • 60
  • 82
  • The link to that sequence generation of all two letter combinations seems to be disabled (probably due to time), is there another reference or link that executes the same thing? – springathing Apr 24 '18 at 15:39
  • 1
    That link still works fine for me? http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server I don't know why you're asking me rather than `Aishvarya` directly though. – Bridge Apr 25 '18 at 07:45
  • Thank you for the link, I didn't see her name in the comments – springathing Apr 26 '18 at 15:28
  • This answer is excellent for my purposes. I simply need to display a row number on a report as a character A-M. Good answer. – Steve-o169 Jan 17 '19 at 19:14