Allow number to start with ZERO when stored in mysql integer field

Question

I need to store phone numbers starting with 0 but whenever i try to store this in MySql table the starting ZERO is removed because no number start with Zero actually.

How to solve this issue? Do I need to change the field type from Integer to another type?

Answer 1

change data type to unsigned-zerofill whatever you are using, float, int, decimal(6,2)... only edit the field to unsigned-zerofill

Answer 2

Phone numbers are not really numbers in the sense that they aren't ordinal. They're just characters - they fact that they are numbers is incidental.

Store them in a varchar and move on :D

Answer 3

Phone numbers can contain other symbols for readability too... a regexp for a phone number looks something like [0-9+-()*#]+. So you need to use a text field for phone numbers, plus some validation.

Answer 4

You can use data type as varchar to solve this.

Answer 5

Phone numbers aren't integers and you will only end up with problems trying to store them as integers, store them as strings instead.

Answer 6

Yes - numeric fields only store the numeric values, not the formatting of those (which paddin with leading zeroes is). You should either

1. change the field type from integer to varchar or char (if # of digits is ALWAYS the same).

2. Store the number as integer BUT prepend 0 in your presentation layer as needed.

Answer 7

You can also wrap the number you want with a lead zero with a function. I made this function to add lead zero if the "string" is smaller than 2 digits (it was used to add lead zeroes to hours and minutes)

function leadZero($num) {
    if (strlen($num) < 2) {
        return "0" . $num;
    } else {
        return $num;
    }

}

If you have say, a number 2 that you want to output as 02, you'd do leadZero(2);

This will only add a zero IF the number is less than 2 digits long ! For instance leadZero(14); will return 14