Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Auto-incrementing with leading zeros

Author  Topic 

marchawinslet
Starting Member

11 Posts

Posted - 2006-08-10 : 08:57:26
Hi All,
I need to set up a kind of identity insert that gives an output in the format: 00001, 00002, 00003 etc. Is there a formatting option for this sort of output using normal identity insert features or do I need to write a function to insert these values (perhaps as text) each time a new record is created?
Sorry if this is really simple but it's only my 4th day in this job!
Marcha

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-10 : 09:02:39
It will be better if you use IDENTITY column...do all formatting (like padding zeros in the begining) at the front-end level if possible.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

marchawinslet
Starting Member

11 Posts

Posted - 2006-08-10 : 09:42:32
Thanks Harsh, I thought of this first of all but the problem I have is that other people wil access the table from their own applications and I really need everyone to see a single unified view of the data without the need for formatting instructions.
Any other ideas?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-10 : 10:07:42
use a computed column to format the zero padding

create table #table
(
id int identity(1,1),
key_id as right('00000' + convert(varchar(5), id), 5),
col int
)

insert into #table (col)
select 10

select * from #table

/*
id key_id col
----------- ---------- -----------
1 00001 10
*/



KH

Go to Top of Page

marchawinslet
Starting Member

11 Posts

Posted - 2006-08-10 : 11:25:29
Thanks KHTan, that's just what I needed!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-10 : 12:11:56
Use only identity column. If you use front end application, use format function to format the number

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -