How to add leading 0's in mysql?

Hi,

I have 2 x int cols in a table in which user has to specify starting and ending numbers of a range. It can be anything, like in some cases its going to be: 000001 to 999999. But as soon as 000001 is added into mysql, it changes it to “1”, meaning all 0’s are omitted.

How can this be preserved ? If somehow this is preserved, then when I have to query the db for a number like say: 000123 then how to search for it ??

Thanks.

The Integer type will not store leading zeros. In mathematics 1 has the same numeric value as 000001 so they are effectively the same number, so will be stored in the exact same way.
I think the key is to understand why you want the leading zeros. Must they be stored in this way?
Usually if you need numbers (or any data) in a specific format, that formatting can be done by your scripts on data retrieval and the database just stores it in the most efficient and searchable way.

1 Like

What Sam says.
And as long as you query your db table with a numeric value, it’ll work fine even with leading zeroes.

WHERE INTFIELD = 123

and

WHERE INTFIELD = 0000123

is equivalent

If the data is a String but typed as a numeric in the table, unless you need to do math operations with the value you should consider ALTERing the field.

Else you should be able to pad() the value when you need to.

Hi,

Bascially there going to be 1000’s of records in the db, and all will be numbers but in different formats, like some can be 1 or 001 or 00001 or 0000001 etc. I don’t have to do any math on these numbers but just range comparison of user input, so 001 to 999 and user inputs 123 then I have to check if its in 001 to 999 or not. Can this be done if I change INT to VARCHAR ??

Secondly I also need to make sure that if range is 001 to 999 and user inputs 10 then it needs to be 010 and not just 10 and then compare 010 from the range. How do I do that ??

Thanks.

If there’s a difference between 1 and 001 it’s not numbers but strings. Use varchar

This you’ll have to do in the language you’re using. PHP?

you do realize that there’s a significant difference between comparing VARCHARs and comparing INTs, right?

you said you have two “int” columns – therefore leading zeros have no meaning

if you want to show leading zeros, do this in your application layer (php or whatever)

In my opinion, if you wanted a field to be fixed at 8 digits and right-zero-padded to adjust the width, you can do

ALTER TABLE yourTable CHANGE numberColumn numberColumn INT(8) ZEROFILL;

Therefore inserting a value of 1234 would result in 00001234 being stored in the column.

this is very misleading

ZEROFILL affects the display of values, not their storage

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.