Как реализовать фильтр по номеру телефона, номера в произвольном формате

[Sufir]

Телефонные номера в тиблице хранятся в произвольной форме, например:
(499)257-1051, 217-69-65 или 8-501-439-0404
Есть идеи, как сделать выборку по номеру? Думаю, как вариант, вырезать все символы кроме чисел и сравнивать с запрошенным. Но не могу придумать как вырезать так, что-бы остались только числа.

SELECT
  regexp_replace('a12b3', '[^0-9]', '') AS phone,
  ...
/* возвращает 12b3? т.е. отрезает только первый нечисловой символ */

Как вырезать все лишние символы из строки или может есть более правильное решение?

 

[Andkorol]

Добавить в таблицу поле, в котором хранить "чистые" номера (только цифры).
Это поле использовать для фильтра - а результат поиска выводить из поля с оригинальным номером.

 

[Redjik]

Добавить в таблицу поле, в котором хранить "чистые" номера (только цифры).
Это поле использовать для фильтра - а результат поиска выводить из поля с оригинальным номером.

Так и делал

 

[Sufir]

Да, похоже придётся так делать... Не хотелось таблицу трогать, т.к. добавление информации прочие основные действия с ней другой человек реализовывал и занимается. Мне только по телефону находить инфу надо. Но вариантов нет, по видимому.

 

[Redjik]

Sufir
сделай вью

 

[Dovg]

а, postgres же - можно сделать функциональный индекс и приводить и искать по нему.

    "plus1_user_mobile_phone_idx" UNIQUE, btree ((
CASE
    WHEN confirm_code IS NULL THEN mobile_phone_cleaner(mobile_phone)
    ELSE NULL::character varying
END))

plus1=> select prosrc from pg_catalog.pg_proc where proname = 'mobile_phone_cleaner';
                                        prosrc                                        
--------------------------------------------------------------------------------------
                                                                                     +
 begin                                                                               +
         return regexp_replace(regexp_replace(_phone, '[^0-9]', '', 'g'), '^8', '7');+
 end;

Это у нас с продакшена.

 

[Sufir]

Dovg, спасибо, отличный вариант. А как же этим индексом в запросе воспользоваться?

 

[Dovg]

Sufir
Он сам подцепится, если ты будешь сравнивать не с полем, а срезультатом этой функции по полю

plus1_targeting=# create index plus1_user_phone_idx on plus1_user(mobile_phone_cleaner(mobile_phone));
CREATE INDEX
Time: 183,937 ms
plus1_targeting=# explain analyze select * from plus1_user where mobile_phone_cleaner(mobile_phone) = mobile_phone_cleaner('+7(965) 416-48-74') ;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using plus1_user_phone_idx on plus1_user  (cost=0.25..1.25 rows=5 width=553) (actual time=0.092..0.092 rows=0 loops=1)
   Index Cond: ((mobile_phone_cleaner(mobile_phone))::text = '79654164874'::text)
 Total runtime: 0.148 ms
(3 rows)

Time: 7,821 ms

Тот индекс, что я привел выше работать не будет, ибо он условный - у нас используется для контроля уникальности нормализованных значений номеров.

 

[Sufir]

Да х.з., я просто вот такой сделал:

CREATE INDEX "idx_crm_client_clean_phone" ON "crm"."crm_client" USING btree ("crm.phone_clean(phone)");

Пишет Seq Scan. Хотя я наверное и без индексов обойтись могу, главное ищет и ищет вполне шустро 0.3-0.4 секунды. Всё равно у меня поиск в кроне делается и не особенно часто - раз пять-шесть в сутки, это не для пользователей.

 

[Sufir]

CREATE INDEX "idx_crm_client_clean_phone"
ON "crm"."crm_client"
USING btree ("crm"."phone_clean"(phone1), ("trash"));

Ага, точно. Условие надо было добавить - там же везде у нас проверка идёт. Так цепляет индекс и работает заметно шустрее.