Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
i am doin a project using C#.net 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
Comments
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
good
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
CREATE TABLE #chill60
(
    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
Returns
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 http://geekswithblogs.net/RobBowman/archive/2011/10/11/generate-sql-server-test-data.aspx[^]
  Permalink  
v2
Comments
geo thomas at 30-Apr-13 3:25am
   
thank you
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
Check the Script..
SELECT 'emp'+RIGHT('0000'+CAST((ROW_NUMBER() OVER(ORDER BY EmpID)) AS VARCHAR(10)),4)
FROM EmpDetails
Regards,
GVPrabu
  Permalink  

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

  Print Answers RSS
0 George Jonsson 175
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2014
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