I got a value in a field called it (EmployeeDetailKey - varchar(10)) with sequential values such as
00001, 00002, 00003....
It is in a table Employeedetail. When ever a new Employee detail has to be inserted I have to get the max(EmployeeDetailKey) and increment by 1 and store it back. If I have 10 employeedetail records that need to be inserted then the same procedure has to follow.
If the max(EmployeeDetailKey) = 00003 then after inserting 10 records it has to be 00013. Later on after inserting let us say 100 records it has to be 00113.
How can I do it in the form of MS-SQL statement.
Please note the column cannot be identity type.
If you prefer a solution without changing the table structure, then:
Cast your zero-padded string value to int. This is easy in SQl server, as it will easily convert such strings to numbers:
SELECT CAST('00003' AS int)
This will return integer value of 3.
Find MAX()
Just perform MAX() on column you've just converted to string, like...
SELECT MAX(CAST(mycolumn AS int)) FROM mytable
Actually, you don't have to do a conversion, as SQL server will sort the values correctly in original string representation.
Increment
This is easy, since you now have the integer value, so...
SELECT MAX(CAST(mycolumn AS int)) + 1 FROM mytable
Convert it back to zero-padded string
SQL Server 2008 is a bit tricky to tame here, since left-padding is not his speciality. However, starting from in SQL Server 2012, there is a FORMAT function available, so, you can use...
SELECT FORMAT(MAX(CAST(mycolumn AS int)) + 1, '00000') FROM mytable
If you have only SQL Server 2005 or 2008 available, you can use REPLICATE() combined with LEN() to get what you need (disclaimer: UGLY CODE):
SELECT REPLICATE('0', 5 - LEN(MAX(CAST(mycolumn AS int)) + 1)) + CAST((MAX(CAST(mycolumn AS int)) + 1) AS nvarchar(5)) FROM mytable
EDIT As Luaan hinted, you can use another padding option (shorter and more readable code):
SELECT RIGHT('00000' + CAST(MAX(CAST(mycolumn AS int) + 1) as nvarchar(5)), 5) FROM mytable