Is there an IDENTITY like column type that generates alphanumeric values? Like 023904F?
+3
A:
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.
KM
2010-04-16 18:48:47
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
2010-04-16 18:51:09
Will the `LEFT(newid(),7)` result in a unique value like `IDENTITY` does?
Raj More
2010-04-16 18:52:23
@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
2010-04-16 18:54:48
@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
2010-04-16 18:56:10
@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
2010-04-16 19:03:12
A:
No - but you could always
- create an INT IDENTITY column
add a computed column such as
ALTER TABLE dbo.YourTable ADD ProductID AS CAST(ID AS VARCHAR(8)) + 'F'or whatever it is you want to do to the ID to make it into your alphanumeric field
marc_s
2010-04-16 18:49:15
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
2010-04-16 19:01:34
@KM: yes, the question does leave quite a bit of room for interpretation.
marc_s
2010-04-16 20:20:04
+2
A:
No: you have to write a function to do it for you. Or concatenate "F" to a number in a computed columns
previous questions:
gbn
2010-04-16 18:50:48
+1, I now think the OP wants something like your second link `Increasing Alphanumeric value in user defined function`
KM
2010-04-16 18:58:09