Click here to Skip to main content
11,805,035 members (67,138 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server
i am doin a project using and sql server.i could auto increment coulum values in the database table.
but i wann them look like emp0001,emp002....

how can i make it like this?
Posted 25-Apr-13 23:17pm
CHill60 at 26-Apr-13 5:21am
Why not just add the formatting when you extract/display the id?
gvprabu at 26-Apr-13 7:03am
U need to display all the rows in a table like If 10 records are there in my table so emp0001,emp0002... emp0010 liek this right.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Use the autoincrement feature but just extract the extra info when you query the database ....
-- Create a sample table
    ID int IDENTITY(1,1),
    TXT varchar(20)
-- insert some sample data (VS2005)
insert into #chill60 select *  from
(select top 10 (char(abs(checksum(NEWID())) % 26 + 65) + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)) AS TXT
FROM syscolumns ac1 cross join syscolumns ac2 cross join syscolumns ac3) as a
Then use something like this to get the data back in the format you want
SELECT 'emp'+replace(str(ID, 7), ' ', '0') as empid, TXT
FROM #chill60
empid       TXT
emp0000001  VD
emp0000002  AI
emp0000003  AS
emp0000004  EB
emp0000005  XU
emp0000006  UR
emp0000007  IW
emp0000008  IA
emp0000009  YS
emp0000010  US
You could put the 'emp'+replace(str(ID,7), ' ','0') bit into a function or even have a database trigger that populates a-n-other column with this derived value based on the auto-incremented ID. There are advantages to using built-in functionality rather than trying to manage the IDs yourself

[EDIT - with acknowledgements to Rob Bowman for the quick generation of test data[^]
geo thomas at 30-Apr-13 3:25am
thank you
Rate this: bad
Please Sign up or sign in to vote.

Solution 2


Check the Script..
FROM EmpDetails

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 305
1 Sergey Alexandrovich Kryukov 268
2 CPallini 210
3 Richard Deeming 190
4 CHill60 150
0 OriginalGriff 3,255
1 Maciej Los 1,990
2 KrunalRohit 1,907
3 CPallini 1,855
4 Richard MacCutchan 1,227

Advertise | Privacy | Mobile
Web04 | 2.8.151002.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100