Phone numbers aren't numbers. They're text. Does it make sense to add two phone numbers together? No? Then they're not numbers :-)Thanks for your mail.
- Boolean is not possible as this is phone number.
So, either store them as varchar or do something fancy.
For instance, you could store them as an bigint (an integer is
probably not big enough), with a smallint indicating how many
preceding zeros there are - but I'd expect a varchar to be
quicker, and it'd certainly be simpler.
You say that 'varchar' gives a performance hit - can you explain
how? Have you done some performance profiling, or have you just
assumed?
If the performance hit is with searching, then you could index on
the phone number cast to an integer, and then do a string
comparison to verify?
Eg
create table people (name varchar, phone varchar);
create index people_phone on people(cast(phone as bigint));
select * from people where cast('07000123456' as bigint)=
cast(phone as bigint) and '07000123456' = phone;
this will use the 'people_phone' index, so searches will be quick.
If 'bigint' indexes are too slow, then you could just index the
last 9 digits and verify with a string compare:
create index people_phone on people(cast(substring(phone from
length(phone) - 8) as integer));
select * from people where cast('000123456' as integer) =
cast(substring(phone from length(phone) - 8) as integer) and
'07000123456' = phone;
-- Paul Paul Smith Computer Services support@xxxxxxxxxx - 01484 855800
