auto in criment id in sql sever

Question

0.00/5 (No votes)

See more:

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 25-Apr-13 23:17pm

geo thomas

Add a Solution

Comments

CHill60 26-Apr-13 5:21am

Why not just add the formatting when you extract/display the id?

gvprabu 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.

2 solutions

Solution 2

Hi,

Check the Script..

SQL

SELECT 'emp'+RIGHT('0000'+CAST((ROW_NUMBER() OVER(ORDER BY EmpID)) AS VARCHAR(10)),4)
FROM EmpDetails

Regards,
GVPrabu

Posted 26-Apr-13 1:06am

gvprabu

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2013-04-25T23:40:00

Use the autoincrement feature but just extract the extra info when you query the database ....

SQL

-- 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

SQL

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[^]