CODEWITHSUNDEEP
mysqlintegerphone-number
EzzDev
EzzDev

Reputation: 10230

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

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?

Upvotes: 12

Views: 42666

Answers (7)

brunobliss
brunobliss

Reputation: 366

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

Upvotes: -1

devasia2112
devasia2112

Reputation: 6034

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

Upvotes: 24

DVK
DVK

Reputation: 129499

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.

Upvotes: 1

Andrew McGregor
Andrew McGregor

Reputation: 34662

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.

Upvotes: 5

Peter Bailey
Peter Bailey

Reputation: 105916

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

Upvotes: 7

myforwik
myforwik

Reputation: 195

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

Upvotes: 3

Karthik
Karthik

Reputation: 3301

You can use data type as varchar to solve this.

Upvotes: 3

Related Questions