Is there a TSQL alphanumeric like IDENTITY column type?

Question

Is there an IDENTITY like column type that generates alphanumeric values? Like 023904F?

possible duplicate of http://stackoverflow.com/questions/2177584/sqlserver-identity-column-with-text — gbn, Apr 16 '10 at 18:51
Are you asking for a IDENTITY column that counts in, for example, base 16 (0-F) or maybe base 36 (0-9,A-Z)? (For comparison: airline reservation systems use base-36 to identify reservations.) — egrunin, Apr 17 '10 at 05:38

score 3 · Accepted Answer · answered Apr 16 '10 at 18:48

3

YES, the uniqueidentifier column, but it is 36 chars in length, try this:

select newid()

output

------------------------------------
53F2103C-C357-429E-A0E8-2DC26666638F

(1 row(s) affected)

you can use it like:

select LEFT(newid(),7)

and get:

-------
50D0F58

(1 row(s) affected)

this will not be unique though.

answered Apr 16 '10 at 18:48

KM.

OK, but that doesn't get added to the table automatically, like an INT IDENTITY - you would have to define a DEFAULT CONSTRAINT on that column to specify `newid()` as default value - close, but not exactly the same, I'd say... – marc_s Apr 16 '10 at 18:51
Will the `LEFT(newid(),7)` result in a unique value like `IDENTITY` does? – Raj More Apr 16 '10 at 18:52
1

@marc_s, after reading the question again and your comment, I'm now not sure of what is being asked. Does the OP want a semi-random char(7) or a sequential alphanumeric, an if so, what are the "rules", leading zeros, 0-9 and a-z or what? OP mentions identity, but is this to be unique? – KM. Apr 16 '10 at 18:54
@Raj More: good point - I doubt taking the first 7 chars off a GUID will be unique for too long a period of time! – marc_s Apr 16 '10 at 18:56
@Raj More, no, in the answer I say this will not be unique though. you'd need all the original 36 chars to be unique. Does the op want this to be unique? they mention `identity` but little other details?? – KM. Apr 16 '10 at 19:03

score 2 · Answer 2 · edited May 23 '17 at 12:03

2

No: you have to write a function to do it for you. Or concatenate "F" to a number in a computed columns

previous questions:

edited May 23 '17 at 12:03

Community

answered Apr 16 '10 at 18:50

gbn

1

+1, I now think the OP wants something like your second link `Increasing Alphanumeric value in user defined function` – KM. Apr 16 '10 at 18:58

score 0 · Answer 3 · answered Apr 16 '10 at 18:49

0

No - but you could always

answered Apr 16 '10 at 18:49

marc_s

1

this goes to what the OP wants, 6 numeric digits + 1 letter? or something else. Looks like all three answers are for a different question, yet the OP didn't specify which of the three questions they are after. – KM. Apr 16 '10 at 19:01
@KM: yes, the question does leave quite a bit of room for interpretation. – marc_s Apr 16 '10 at 20:20

3 Answers3