The requirement is that I have to select the max of column value from a table and the add 1 to it and insert the new record.
The column is varchar(5). So if the number after adding 1 is less than 5 digits then, zeros should be added before the number to make the field of 5 digits.
For ex:
The input field has values:
00000
00001
00002
00003
Now the max will return 00003.
After adding 1 it becomes 4 and gets inserted with value 4. But it should be inserted as 00004.
I tried using len function and padchar also but not able to do it.
In the testing I've done, I use Shane's method (slap extra zeroes on the front and truncate to length) because it is signficantly faster than any other method I've found. It is basically two operations, pad and truncate with no logical tests to perform.