SQLServer IDENTITY Column with text

Question

How would I create an IDENTITY column in SQLServer with text in the column?

Example:

ABCD-987065
ABCD-987066
ABCD-987067

Answer 1

Nope, sorry. The identity "property" can only be placed on columns with the integer or decimal data type.

Answer 2

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.

Answer 3

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.

Answer 4

CREATE TABLE BikeParts (
    BikeParts_GUID AS 'ABCD-' + RIGHT(REPLICATE('0', 8) + CONVERT(VARCHAR, BikePart_ID), 10),
    BikePart_ID INT IDENTITY(1, 1),
    BikePart_Name VARCHAR(100)
)

INSERT INTO BikeParts VALUES ('Break Cable')
INSERT INTO BikeParts VALUES ('Seat Cover')
INSERT INTO BikeParts VALUES ('Head Light')
INSERT INTO BikeParts VALUES ('Tail Lamp')

SELECT * FROM BikeParts

Answer 5

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.

Answer 6

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...)