How would I create an IDENTITY column in SQLServer with text in the column?
Example:
ABCD-987065 ABCD-987066 ABCD-987067
Asked
Active
Viewed 3.2k times
8 Answers
17
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
- 295,962
- 43
- 465
- 541
-
Are the columns included in the combined Id required to be in front of the `combinedId` column? – May 10 '13 at 21:05
1
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
- 142,592
- 28
- 206
- 200
1
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
- 10,891
- 6
- 33
- 52
1
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
Sebastian Brosch
- 42,106
- 15
- 72
- 87
Aravinth S
- 29
- 5
0
No. The actual column type must be an int or bigint works too.
Randy Minder
- 47,200
- 49
- 204
- 358
-
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? – Feb 01 '10 at 15:11
0
Nope, sorry. The identity "property" can only be placed on columns with the integer or decimal data type.
Philip Kelley
- 39,426
- 11
- 57
- 92
0
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
- 28,545
- 12
- 80
- 110
-
That is, assuming the `ABCD` part doesn't auto increment too (but I'm assuming it does). – Codesleuth Feb 01 '10 at 15:14
-
1Codesleuth - 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 Feb 01 '10 at 15:18
-
roygbiv - You need a cast on the integer field so I've updated my answer. – Mark Brittingham Feb 01 '10 at 15:24
0
Check the answers here...
-
1Oh dear! How dare I suggest a solution and get anonymously downvoted (like other folk here) – gbn Feb 01 '10 at 15:54
-
2I see no reason to down vote you, your answer relates to the question. some people I don't know how they think. take it back to normal – Kronass Apr 13 '10 at 11:00