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)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.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