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?

Tarpon

Why'd this get voted down? The question was clear and answerable. – Incurious

I know you probably don't care, but I apologize for my earlier comments and am deleting them (except for the first one before I got frustrated). I was out of line, and I am sorry. – Menu

This has been answered here on stackoverflow: #611840 – Staccato

it works only till 999, after that, if you have 1000 in number then it results in 000 and 1001 leads to 001 – Heroics

Answer 1

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.

Godden

+1. I suppose it was more useful to show how to get the display he wants than to tease him for not understanding numbers. – Menu

thanks for reply, but i need to store it in the Database in the 001 format insted of 1,2... – Tarpon

@Ranjana: why does it need to be stored? Who's consuming this that can't take advantage of a little casting and formatting? – Godden

Don't you mean || instead of +? – Jesher

Answer 2

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.

Lamoreaux

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

Incurious

Answer 4

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.

Andy

That's right.. It's a matter of formatting the output. This is the second question I've seen like this in the last two days.. For a programmer not to understand that 001 is the same as 1 is just scary. The other guy wanted to store dates in the DateTime datatype without the milliseconds so they would not show up when using .ToString(). Sheesh. – Menu

+1. This is most definitely a display issue, not a storage issue. @Ranjana: the client should be responsible for padding. – Godden

Why would you store this? you could just create a view on the table with the logic to pad the integer as appropriate, then reference the view and be done. – Varipapa

Answer 5

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

Varipapa

Answer 6

Here is another method:

create table TEST_T (ui int NOT NULL identity, name varchar(10))
insert into TEST_T values ( 'FRED' )
select NAME, ui, RIGHT('0000' + LTRIM(STR(ui)), 4) as ui_T from TEST_T
go
/* NOTE: A view could be created with a calculated column instead of the identity column. */
create view TEST_V as select NAME, RIGHT('0000' + LTRIM(STR(ui)), 4) as ui_V from TEST_T go
go
select * from TEST_V
drop view TEST_V
drop table TEST_T

Not quite as much data duplication(?) as adding a column to the table and no need to specify the column in the select statement.

Dwarfism

Answer 7

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.

Jesher