I am trying to generate a unigue code for every user in my table. This code sholud look like:
"S" & Current Year & Unique Number.
And this Unique Number consists of 5 digitis such that:
First ---> 00001 ----> SXX00001
Second ---> 00002 ----> SXX00002
Third ---> 00003 ----> SXX00003
Fourth ---> 00004 ----> SXX00004
and so on.....
What I do is that I select the last 5 digits of the code
and convert it to integer and and then add 1. It works but the result will be ----> SXX5 and not ----> SXX00005,
meaning it ignores the zeros on the left?
Can any body help me to fix this code such that the new id does not ignore the zeros on the left
My code is:
select @Mv = RIGHT(SID, 5)From SRegisteration
set @Mv = CAST(@Mv AS int) + 1

Thanks

To get the code without ignoring the zeros, use the LPAD function.