2

I have different phone formats stored in my db, such as :

727.170.4799
1-416-958-6390
1.561.374.4268
(813) 929-5892

There are approximately ~2 million records in this table so modifying would take a long time. And I need to find records by performing phone lookup.

One thing I control is the input, I can guarantee that it's going to be numeric. But what I don't know is whether they're going to type in numeric number with or without the country code so taking this number as an example 1-416-958-6390 both of these used in lookup:

14169586390
4169586390

Should return the same row (1-416-958-6390)

How do I do phone for phone numbers as efficiently as I can?

This is what I got but it's super slow (as expected) :

select * from patients
WHERE ( REPLACE(phone_number, '-', '') LIKE '%4169586390' )
1
  • It's postgresql. I need to do multiple replace, for dots(.), white spaces(` ), dashes(-), opening (` and closing parentheses ). I have tried (not successfully) to use regex as a replacement pattern instead of just dash, was not successful, so I ended up executing multiple replaces. Commented Dec 21, 2019 at 11:29

3 Answers 3

7

To clean up the numbers use a regex that removes everything that's not a numeric digit:

regexp_replace(phone_number, '[^0-9]', '', 'g') 

You can create an index on that, but a regular B-Tree index won't be used LIKE conditions where the wildcard is on the left.

In Postgres you have two choices: you can create a regular B-Tree index on the reverse value and use that instead:

create index on patients ( reverse(regexp_replace(phone_number, '[^0-9]', '', 'g')) );

Then use that expression and compare it with the reversed input:

select * 
from patients
where reverse(regexp_replace(phone_number, '[^0-9]', '', 'g')) like reverse('4169586390')||'%'

The other choice is to install the pg_trgm extension and create a trigram index on the expression. That way you can use the input "as is":

create index on patients 
   using gist ( regexp_replace(phone_number, '[^0-9]', '', 'g') gist_trgm_ops);

That index is then usable for:

select * 
from patients
where regexp_replace(phone_number, '[^0-9]', '', 'g') like '%4169586390'

However, GiST indexes are bigger and a bit slower to maintain than B-Tree indexes.


If you don't want to repeat that regex expression in every query you can create a view that includes that, e.g.:

create view patients_clean_phones
as
select ... other columns ..., regexp_replace(phone_number, '[^0-9]', '', 'g') as clean_phone
from patients;

Then use (assuming you are using the GiST index, so no reverse required):

select *
from patients_clean_phones
where clean_phone like '4169586390%';

Postgres is smart enough to still use the index in that case.


If you are on Postgres 12, an alternative to the view is to use a computed column with the regex expression, then create the index on the computed column.

5

Plan A: (Works for US and Canada)

Remove punctuation and leading "1" from both the column and the incoming value is all that is really needed. This is a generic solution to many searching problems -- sanitize both the table and the incoming query with the intent of making the lookup efficient.

Plan B:

  1. Have another column with the REVERSE(num), call it rev. It could be a "generated" column. Index it.
  2. Reverse the incoming value: REVERSE("1.561.374.4268")
  3. Take the first 4 digits of it: LEFT(REVERSE("..."), 4) = "8624"
  4. Find potential results: WHERE rev LIKE CONCAT(LEFT(REVERSE(inc), 4), '%')`

Note reversing avoids having a leading wildcard, which prevents effective us of an index. You now have a small list of possible values (about 200 of the 2M).

  1. Perform REGEXP checking for all the formats and the rest of the digits.

Even better would be to remove all punctuation from the reversed list. Then the only variation is whether it ends with 1. If you are limited to US and Canada, simply remove the "1" from both the rev column and the incoming value.

If you are checking for some variant of "987-654-3210", either of these will finish checking the 200:

phone REGEXP "987.*654.*3210"  -- no anchoring needed

rev REGEXP "^0123.*456.*987"   -- one anchor needed

So...

Plan C:

  1. Add the column rev computed from REVERSE(phone)
  2. Index it.
  3. Then, when searching for some variant of "987-654-3210", do

    WHERE rev LIKE '0123%' AND rev RLIKE "^0123.*456.*987"

Note: LIKE is willing to use the index; RLIKE is not.

Cleansing

Older versions:

REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(num, '-', ''),
             ')', ''),
             '(', ''),
             ' ', ''),
             '.', '')

In MySQL 8.0 or MariaDB 10.0:

REGEXP_REPLACE(num, '[^0-9]', '')

Also (any version), to remove the leading '1', here is one way:

RIGHT(num, 10)
0
0

This is what I would do:

  1. write a PLV8 function to use Google's libphonenumber to format them in a standard format, ie E.164
  2. write them back to the database in another table (w the key)
  3. create a new table, with the same structure/data as the old table, with the new phone numbers
  4. drop the old table, rename the new table with the old table's name

That should be pretty fast

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.