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.

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_

more