Auto-incrementing a number that is part of a string value in a SQL Server database

Question

How can I auto-increment a number that is part of a string value in a SQL Server database?

For example, here is my table:

EMP_ID  EMPNAME  EMPSECTION
EMP_1   ROSE     S-11
EMP_2   JANE     R-11

When I add a new record, what I would like to do is automatically increment the number that follows EMP_. For example, EMP_3, EMP_4, etc.

Do you really need the `EMP_` prefix? Can't you simply have an identity column? — Andre Calil, Aug 21 '12 at 18:23
You need to write a function to handle this. Otherwise, remove the `EMP_` to make it an auto incrementing integer field, and then just concatenate `EMP_` to it when fetching results. — Kermit, Aug 21 '12 at 18:23
I think you need this.. http://stackoverflow.com/questions/8170950/how-do-i-add-string-with-auto-increment-value-in-sql-server — user1521554, Aug 21 '12 at 18:28

score 0 · Answer 1 · answered Aug 21 '12 at 18:28

0

one option is to have a table that has an autoincrement id field. Then you can write a trigger on this table that on insert, fires an insert on the autoincrement table and fetches the current value. Then concat that value on to the end of EMP_

answered Aug 21 '12 at 18:28

Brian

2,229
17
24

i'm sorry. can you give a sample? because i'm new in programming – Carlo Adap Aug 21 '12 at 18:30

codingbiz · Answer 2 · 2012-08-21T18:51:10.990

0

You can have identity column in your table and display 'EMP_' appended to its value in your user interface. If you want to do it custom way, you'll need a sequence table

Create a sequence table

Sequence
-------------------
Seq_Name  | Seq_Val
-------------------
EMPLOYEE  | 0

Then you need a Stored Procedure to perform this

BEGIN
declare @curVal int

Select @curVal = Seq_Val+1 From Sequence Where Seq_Name='EMPLOYEE'

UPDATE Sequence SET Seq_Val = Seq_Val+1 Where Seq_Name='EMPLOYEE'

Insert into Employee Values ('EMP_'+Cast(@curVal As Varchar), 'Rose', 'S-11')
END

edited Aug 21 '12 at 18:51

answered Aug 21 '12 at 18:35

codingbiz

26,179
8
59
96

That doesn't work. You've just introduced a race condition In between your `select ...from Sequence` and your `update Sequence`. There is no guarantee that somebody else won't (in fact, it's pretty much guaranteed that somebody **will**) creep in there and hose you. – Nicholas Carey Aug 21 '12 at 20:52
I think the OP should go with identity column. We used this approach in my place of work and I was not aware of the implication – codingbiz Aug 21 '12 at 21:17
See my answer here, http://stackoverflow.com/a/4822494/467473 , about how to do what you want to do by using an interlocked update in SQL Server. It works for SQL Server, but I wouldn't guarantee it for any other relational DB. – Nicholas Carey Aug 21 '12 at 22:03

score 0 · Answer 3 · answered Aug 21 '12 at 18:37

By C# It's very to do , each time you want to insert a new row before inserting that row you should generate the key by following these steps :

1- get a list of your ID field 2- Do a for each loop to find tha maximum key value , something like this :

int maxID=1;
for each(var l in list)
{
if(int.Parse(l.ID.Replace("EMP_",""))>maxID)
{
maxID=int.Parse(l.ID.Replace("EMP_",""));
}
}
maxID=maxID+1;

string ID="EMP_"+maxID.Tostring();

And ID is your new ID !

but if your application is accessed by multiple programs (example : consider It's a website) I really don't suggest you to do something like this cause : 1. It's time consuming 2. In some condition same key value from multiple clients might be generated and you will have error while inserting .

score 0 · Answer 4 · answered Aug 21 '12 at 20:49

You can do something like:

create table dbo.foo
(
  id int not null identity(1,1) , -- actual primary key
  .
  .
  .
  formatted_id as 'emp_' + convert(varchar,id) , -- surrogate/alternate key

  constraint foo_PK   primary key ( id           ) ,
  constraint foo_AK01 unique      ( formatted_id ) ,

)

But I can't for the life of me think of just why one might want to do that.

Auto-incrementing a number that is part of a string value in a SQL Server database

4 Answers4

Linked