Preface
I am currently rewriting an application which has a pretty interesting way of doing some things. Those certain things they want done the same way and I don't have much control of the data or it's structure. Mainly because they want it to be backwards compatible with their old program along with other programs used throughout the company.
Problem
They use a public facing employee ID throughout the company. It is the letter E and 4 digits. It is stored in the database as char(5). So an example ID is E1234. Assigning this ID is a manual process. The problem is it is starting to be hard to maintain. Periodically they take the last ID used and make a list of several hundred usable IDs. They then cross out the IDs as they are used. Wash, rinse, repeat. On paper they are about 2/3s burned through every ID. Database wise there is a lot of unused IDs and IDs that can be decommissioned and they realistically have 2/5th of the possible IDs used. Fortunately it is unlikely this company will ever have 9,999 active employees.
Solution
In the new program the above problem can go away if we can determine and assign an available ID for for them as they create a new employee. I have found a lot of solutions looking for the next usable number but they seem to cater only if you are using an int and not a char(5) with a letter. Is it possible to do what I want and if so how can I?
If you order by a substring of your employeeID column, you'll be able to get the highest value. From there, you can add 1. Working sample:
DECLARE @tbl TABLE (col1 CHAR(5));
INSERT INTO @tbl
( col1 )
VALUES ( 'E0521' ),
( 'E1542' ),
( 'E1543' )
-- prove to yourself that the ordering is correct
SELECT *
FROM @tbl
ORDER BY CAST(SUBSTRING(col1, 2, 4) AS INT) DESC
DECLARE @max INT;
--get the max value
SELECT @max = MAX(CAST(SUBSTRING(col1, 2, 4) AS INT))
FROM @tbl
-- increment the max value and put E back on the front
SELECT 'E' + cast (@max + 1 AS CHAR(4))