CODEWITHSUNDEEP
mysqlsql
Jamil Rahman
Jamil Rahman

Reputation: 335

Why phone numbers in MySQL database are being truncated

I have created a database table in mySQL of which two column names are "landPhone" and "mobilePhone" to store phone numbers (in the format of: 123-456-8000 for land and 098-765-6601 for mobile). These two columns' data type are set to VARCHAR(30). The data have been inserted in the table. But after SQL query, I found the phone numbers have been truncated. It shows (above two data for example) only first 3 digits (123) for landPhone and only first 2 digits after removing the leading '0' (98) for mobilePhone.

Why this is happening ?

Upvotes: 0

Views: 2202

Answers (3)

Jamil Rahman
Jamil Rahman

Reputation: 335

Thanks all for your solution. As cHao suspected, it was me who did the mistake. When I first time created the table, I declared the datatype of the phone columns as INT, later I corrected them to VARCHAR().

When I dropped the table and inserted the same data to the new table, it is working fine.

That sounds exactly like the result you're describing. Even if you end up stuffing the result into a string field, it's too late -- the data has already been corrupted. ..cHao

Question to understand: Why mySQL doesn't override the previous datatype with the new one ?

Upvotes: 0

cHao
cHao

Reputation: 86525

Phone numbers are not actually numbers; they are strings that happen to contain digits (and, in your case, dashes). If you try to interpret one as a number, two things typically happen:

  • Leading zeros are forgotten.
  • Everything from the first non-digit to the end of the string is stripped off.

That sounds exactly like the result you're describing. Even if you end up stuffing the result into a string field, it's too late -- the data has already been corrupted.

Make sure you're not treating phone numbers as integers at any point in the process.

Upvotes: 8

Ashish Gaur
Ashish Gaur

Reputation: 2050

You must use 

insert into sample values('123-456-8000', '098-765-6601' )

instead of 

insert into sample values(123-456-8000, 098-765-6601 )

see this SQLFiddle.

Upvotes: 6

Related Questions