How to search phone numbers, ignoring dashes, bracets and spaces in MySql or PostgreSql?

Question

In database(users table) I have phone numbers in this format:

(619) 745-0863
(619) 745-0864
(619) 745-0865

I want to search by phone numbers, ignoring spaces, dashes and bracket, this means if I search by this "6197450863" I want in results first row which have this phone number "(619) 745-0863".

I have code that works :

SELECT * FROM users where 
REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ')', ''), '(', ''), ' ', '') 
LIKE '6197450863'

But this is slow and awkward. Is there any way to do this better ? I currently use MySQL but I will switch to PostgreSQL if there is a better way.

I know that I can achieve this with search engines like Elastic Search and I know that someone will tell me use Elastic Search even I say explicitly that this question is about how to achieve this with mysql or postgreSql :) If there is no better way, just say so, don't tell use Elastic search or something similar.

This question is actually about how to use full text search in RDBMS instead of replacing text.

Answer 1

In Postgres probably the most efficient way would be to use translate().

with users(phone) as (
values
    ('(619) 745-0863'),
    ('(619) 745-0864'),
    ('(619) 745-0865')
)
select *
from users
where translate(phone, '() -', '') = '6197450863'

     phone      
----------------
 (619) 745-0863
(1 row)

Answer 2

With PostgreSQL to remove all chars not numbers in a phone number :

SELECT regexp_replace('(12s3)-456-6356(a+sdk', '[^0-9]', '', 'g');

So with your sample :

SELECT * 
  FROM users 
 WHERE regexp_replace(phone, '[^0-9]', '', 'g') = '6197450863';

Hope this help.