CODEWITHSUNDEEP
mysql
user2816819
user2816819

Reputation: 21

how to specify the phone number?

while creating the table in mysql 5.1 im getting an error while i specify the phone number as integer.

error is 'out of range value'.

i know i can specify the number as varchar. i just want to know is their any other method ? 

create table emp(
    ename varchar(20), 
    ecity varchar(10),
    salary double,
    enumber integer,
    eaddress varchar(40),
    depttname varchar(25)
);

Insert:

insert into emp 
values('priya','delhi',40000,9958465876,'b-64 janak puri new delhi','manager');

ERROR 1264 (22003): Out of range value for column 'enum' at row 1

Upvotes: 1

Views: 104

Answers (4)

Sammitch
Sammitch

Reputation: 32242

Phone numbers are one of the few exceptions to the rule of not storing numbers as strings. This is because:

  • Oftentimes the numeric representation is larger than what can be stored in the field.
  • Some phone numbers have leading zeroes that should not be stripped off, eg: 0014255550177
  • You may want/need to store non-numeric characters with the phone number like +

Just make sure that you go the extra mile with your input validation on the field so that the phone numbers are stored in a consistent format if you want to do something like search for records matching a particular phone number.

ie: 780-555-1234, 780 555-1234, 780.555.1234 should all have their superfluous characters removed and be stored as 7805551234.

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You may try to use varchar to store phone numbers. But if there is any specific reason to take it then you can use BIGINT as currently you are facing the problem of range of int which is ,maximum at 4294967295.

Upvotes: 2

Talandar
Talandar

Reputation: 116

An Integer in mysql has a max value of 2147483647, or 4294967295 if you use an unsigned integer. This means that most values will be out of range by default. You could use a BIGINT, with a signed range of 9223372036854775807 and an unsigned range of 18446744073709551615. That would be enough, even with international dialing taken into account, I think.

Upvotes: 2

Jinal Shah
Jinal Shah

Reputation: 315

Just change the datatype for enumber to BigInt might do the trick

Upvotes: 2

Related Questions