How would I create an IDENTITY column in SQLServer with text in the column?
Example:
ABCD-987065 ABCD-987066 ABCD-987067
Thank you. Would you care to elaborate a little as to how one would create such a column. Or perhaps provide a reference to what you have stated?
2010-02-01 15:11:37
A:
Nope, sorry. The identity "property" can only be placed on columns with the integer or decimal data type.
Philip Kelley
2010-02-01 15:10:41
Thank you. Would you care to elaborate a little as to how one would create such a column. Or perhaps provide a reference to what you have stated?
2010-02-01 15:12:14
This one totally depends upon the nature of the primary key value they require--that is, what do they want to put in as "ABCD", and why, and how is it determined, and... it led into a long series of design questions, and as yesterday was a very busy day at work, I elected not to follow up on it. (Ok, also it was an easy answer, and this time I was going for the quick and not the good/robust reply.)
Philip Kelley
2010-02-02 15:15:58
A:
No but you can create your Select statement in such a way as to return the code you want:
Select 'ABCD-' + Cast(IdColumn as varchar) as IdColumn From MyTable Where (....);
This assumes that you have a column called IdColumn in your table and that the "IDENTITY" property has been set to "true". That is, highlight the field in the table designer of SQL Server Management Studio and you'll see a window for the properties at the bottom.
If the 'ABCD' part may change, you could place this value in another field and then retrieve as so:
Select PrefixField + '-' + Cast(IdColumn as varchar) as IdColumn From MyTable Where (....);
You could, of course, create a View to do this for you or even a computed field as well. That way, the return value is built in to the query and you don't have to remember to enter all of this each time.
Mark Brittingham
2010-02-01 15:13:13
That is, assuming the `ABCD` part doesn't auto increment too (but I'm assuming it does).
Codesleuth
2010-02-01 15:14:41
Codesleuth - how would "ABCD" increment? were you assuming that this was a HEX number? With the dash present, I figured that this was a fixed prefix. I've changed my answer, though, to handle changes in the prefix.
Mark Brittingham
2010-02-01 15:18:55
roygbiv - You need a cast on the integer field so I've updated my answer.
Mark Brittingham
2010-02-01 15:24:35
A:
You'd have to not use an IDENTITY column, but generated the ids/strings yourself.
Far better to format the IDENTITY column for display instead, especially if the string part is constant for all records - makes indexing/querying more performance and saves on db space.
If records may have a different string section (i.e. not all starting with "ABCD-"), then you could store that as a separate field.
AdaTheDev
2010-02-01 15:14:23
A:
In addition to the other answers, you could create a computed column on the table to provide what you are asking for.
CREATE TABLE dbo.MyTable
(
Id int NOT NULL PRIMARY KEY,
CombinedId AS 'ABCD-' + CAST(Id as varchar(16))
)
Or:
CREATE TABLE dbo.MyTable
(
Id int NOT NULL PRIMARY KEY,
PrefixField varchar(16),
CombinedId AS PrefixField + CAST(Id as varchar(16))
)
(Your question doesn't say whether the prefix is intended to be fixed or not...)
Mitch Wheat
2010-02-01 15:21:06
A:
You could increment your string values with a function like this:
http://www.sqlservercentral.com/scripts/Miscellaneous/31448/
I'm curious, though, why you're looking to use an alpha-numeric key rather than just a numeric one.
Kevin Fairchild
2010-02-01 15:25:16