Click here to Skip to main content
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 26-Apr-13 0:17am
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 /\jmot 200
1 Zoltán Zörgő 175
2 Richard Deeming 170
3 Peter Leow 150
4 Sergey Alexandrovich Kryukov 150
0 Sergey Alexandrovich Kryukov 9,328
1 OriginalGriff 6,831
2 Peter Leow 4,477
3 Zoltán Zörgő 3,984
4 Richard MacCutchan 2,832

Advertise | Privacy | Mobile
Web02 | 2.8.150129.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