Get max + 1 without removing zeroes in SQL server : I have a varchar field contains numbers in this format "00001" , "00002" etc
when I try to get the next number by using Max(Field) + 1 I get an integer "3" for example.
how can I get the resutl "00003" instead of "3"?



What I have tried:

Reply Modify the comment. Delete the comment.
here's an working example I've just figure it out, but i think there's must be an easier way:
SELECT TOP (1) { fn REPEAT(0, LEN(ItemId) - LEN(MAX(ItemId) + 1)) } + CAST(MAX(ItemId) + 1 AS varchar(7)) AS Expr1
FROM Items
GROUP BY ItemId
ORDER BY ItemId DESC
the last query gives the correct result "0004916"