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

Sufir

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

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

Andkorol

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

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

Я не волшебник, я только учусь
Да х.з., я просто вот такой сделал:
PHP:
CREATE INDEX "idx_crm_client_clean_phone" ON "crm"."crm_client" USING btree ("crm.phone_clean(phone)");
Пишет Seq Scan. Хотя я наверное и без индексов обойтись могу, главное ищет и ищет вполне шустро 0.3-0.4 секунды. Всё равно у меня поиск в кроне делается и не особенно часто - раз пять-шесть в сутки, это не для пользователей.
 

Sufir

Я не волшебник, я только учусь
PHP:
CREATE INDEX "idx_crm_client_clean_phone"
ON "crm"."crm_client"
USING btree ("crm"."phone_clean"(phone1), ("trash"));
Ага, точно. Условие надо было добавить - там же везде у нас проверка идёт. Так цепляет индекс и работает заметно шустрее.
 
Сверху