Store number with leading 0's in DB

[IClarke]

I need to store a number in my SQL database in the form 0001, 0020, 0134 i.e. fixed at 4 digits. Does anyone have any idea how to do this ? This is a new requirement to an existing system hence i'd rather make a database change if possible ratehr than having to make changes to lots of ASP pages ...

Thanks in advance
Ian.

[idover]

i've never done this in microsoft's sql server, but i have done it in MySQL... in MySQL, you simply add the term ZEROFILL to your column description, also make sure to define the length that the number should be... in one situation, i had to have exactly six digits, so it was

fieldName int(6) zerofill

... i'm sure that mssql has something similar... i'll look in a bit...

if this is something that could be done after the data is retrieved from the server, you could easily convert it like this:


intResultNum = CInt(Right("0000" & intCurrentNumber, 4))


good luck!

[IClarke]

Thanks for that, i ended up using a custom format function (as vbscript has'nt got one) that does the job, but your CInt solution looks even better !

Ian.