Re: Data Type to store Leading Zero(0)

On 15/04/2021 14:47, soumik(dot)bhattacharjee(at)kpn(dot)com wrote:
>
> Thanks for your mail.
>
> * Boolean is not possible as this is phone number.
>
Phone numbers aren't numbers. They're text. Does it make sense to add
two phone numbers together? No? Then they're not numbers :-)

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(at)pscs(dot)co(dot)uk - 01484 855800

--

Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53

Sign up for news & updates at http://www.pscs.co.uk/go/subscribe