SQL Identity with leading padded zeros

Question

I have marked a column as Identity in my table

create table Identitytest(
    number int  identity(1,001) not null,
    value varchar(500)
)

I need the identity column to be incremented as 001,002,003, etc.

The database shows that it is inserting as 1,2,3, etc.

How can this be done?

Answer 1

Why do you need that? As an integer, 001 is the same as 1. If what you want is that for display or other purposes, create another column and do your work there (you may do it as part of a trigger on the table, on insert, that looks at the newly inserted row, and creates the entry in the column appropriately.

Answer 2

If you want to display your number column with leading zeros, just pad it in your SELECT statement. It's a number, it will NOT store with leading zeros as an integer.

SELECT RIGHT('00000' + CAST([number] AS varchar(5)) , 3)
FROM IdentityTest

The 3 is the number of characters you want total in the output display.

Answer 3

If you require both the auto-incrementing number (which can only be a number) and an alphabetic representation of the number, you might consider looking at computed columns.

Here's a few links to get you going:

• http://www.mssqltips.com/tip.asp?tip=1682
• http://msdn.microsoft.com/en-us/library/ms191250.aspx
• http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx

Answer 4

As the others have already rightfully pointed out - an INT never has leading zeroes - it just holds the value, that's all (and that's good that way).

If you need some additional formatting, you could always add a computed column to your table, something like:

ALTER TABLE dbo.Identitytest
  ADD DisplayNumber AS  RIGHT('000' + CAST(number AS VARCHAR(3)) , 3) PERSISTED

This way, your INT IDENTITY will be used as an INT and always contains the numerical value, while DisplayNumber contains 001, 002, ... 014, 015, ..... and so forth - automagically, always up to date.

Since it's a persisted field, it's now part of your table, and you can query on it, and even put an index on it to make queries faster:

SELECT value FROM dbo.IdentityTest WHERE DisplayNumber = '024'

And of course, you could use just about any formatting in the definition of your computed column, so you could also add a prefix or something:

ALTER TABLE dbo.Identitytest
  ADD DisplayNumber 
      AS  'ABC-' + RIGHT('000' + CAST(number AS VARCHAR(3)) , 3) PERSISTED

So in this case, your DisplayNumber would be ABC-001, ABC-002, ... and so on.

You get the best of both worlds - you keep your INT IDENTITY which is numerical and automatically increased by SQL Server, and you can define a display format any way you like and have that available at any time.

Answer 5

I need the identity column to be incremented as 001,002,003, etc.

The database shows that it is inserting as 1,2,3, etc.

SQL databases store values, not the literals you used to write those values. 002 is 2. Just like 1 + 1 is 2. Would you expect SELECT 1 + 1 to display the string "1 + 1" instead of 2?

If you want the leading zeros to be stored in your column, you have to use a character type. But then you can't use AUTOINCREMENT/IDENTITY.

What you probably really want is something like printf("%03d", number) in program that reads from the database.

Answer 6

i've got a table where i'm storing an integer, but the users want to see it a XXX, even if it has zeroes, so i wrote this code

declare @a int
set @a=1


select replicate('0',3-len(@a))+ cast(@a as varchar(4))