Как заменить некоторые символы в номере телефона с помощью SQL?

У меня есть таблица Contact, содержащая все контакты пользователей с телефонными номерами, и мне нужно сделать некоторые преобразования для телефонных номеров.

Мне нужно просмотреть все номера телефонов и:

  1. удалить следующую последовательность символов (0), если она присутствует в номере телефона;

  2. добавить префикс +<country_code>, если он отсутствует и если номер телефона вставлен;

Пример:

Номер телефона:
+1 (0) 121 121 121
необходимо преобразовать в:
+1 121 121 121

Номер телефона:
(0) 121 121 121
необходимо преобразовать в:
+1 121 121 121

Номер телефона:
121 121 121
необходимо преобразовать в:
+1 121 121 121

По пункту 1:

Мы можем выбрать все номера телефонов с запросом:

select phone from contact where phone like '%(0)%';

а как убрать ту последовательность (0) только из номера? Как создать для этого запрос на обновление?

По пункту 2:

Как распознать, что номер телефона не содержит прямой номер страны (префикс +<country_code>), и добавить правильный, если он отсутствует? Возможно, этот запрос подойдет для выбора этих чисел:

select phone from contact where phone not like '%+%';

Мы можем это предположить. Предположим также, что у нас есть столбец страны в таблице контактов, и для списка кодов стран мы можем создать временную таблицу сопоставления на основе этого -> http://countrycode.org/. Мы можем просто создать временную таблицу, содержащую сопоставление кода страны ‹-› префикса страны, как показано ниже.

Я предполагаю, что должна быть возможность вызвать запрос на обновление, который на 1-м шаге выберет все телефонные номера в неправильном формате, а на 2-м шаге будет обновлен с новым правильным значением, верно? Я не очень хорошо знаю SQL, поэтому, пожалуйста, помогите мне создать такой SQL-запрос?

Таблица КОНТАКТ:

+-----+-----------+----------+---------+------------------------+
| id  | firstname | lastname | country | phone                  |
+-----+-----------+----------+---------+------------------------+
| 100 | Frank     | Grob     |   PL    | +48 22 121 121 121     | <- OK
| 101 | Bob       | Bloby    |   PL    | (0)22 121 121 121      | <- Wrong
| 102 | Alice     | Wonder   |   US    | +1 (0) 121 121 121     | <- Wrong
| 103 | Chris     | Black    |   US    | +1 (0) 121 121 121     | <- Wrong
| 104 | Rocky     | Rocky    |   US    |  +1 (0) 121 121 121    | <- Wrong
+-----+-----------+----------+---------+------------------------+

Таблица COUNTRY_MAPPING:

+-----+--------------+--------+
| id  | country_code | prefix |
+-----+--------------+--------+
| 100 | PL           | 48     |
| 101 | US           | 1      |
+-----+--------------+--------+

sql regex oracle regexp-replace oracle11g
person Roman    schedule 23.01.2015    source источник
comment
На первый взгляд, я бы сказал, загляните в regexp_replace   -  person wvdz    schedule 23.01.2015
comment
Для этого нет простого решения. Например, вы можете посмотреть похожие вопросы (php + regexp) здесь, stackoverflow.com/questions/12551473/. @Tomáš Fejfar цитирует стандартное требование, которое довольно сложно выполнить с помощью регулярных выражений, en.wikipedia.org /вики/E.164.   -  person Patrick Bacon    schedule 23.01.2015
comment
@ Патрик, я не согласен. Это несложно сделать в Oracle, который поддерживает регулярные выражения POSIX.   -  person David Faber    schedule 24.01.2015
comment
@David Faber Да, если мы просто очистим телефонные номера с (0) или без прямого номера страны, это выполнимо (ваше решение выполняет это). Если мы хотим убедиться, что номер соответствует E.164, это сложно (здесь не требуется).   -  person Patrick Bacon    schedule 24.01.2015

Ответы (1)


arrow_upward
2
arrow_downward

Это не должно быть сложно. Если у вас есть столбец кода страны в таблице CONTACT и отдельная таблица COUNTRY_MAPPING, то такой запрос может выглядеть так (к вашему сведению, префикс Польши — 48, как вы правильно указали в таблице CONTACT, а не 22, как в таблице COUNTRY_MAPPING). ):

SELECT c.id, c.firstname, c.lastname, c.country, c.phone
     , REGEXP_REPLACE(REGEXP_REPLACE(c.phone, '\(0\)\s*'), '^([^+])', '+' || cm.prefix || ' \1') AS new_phone
  FROM contact c, country_mapping cm
 WHERE REGEXP_LIKE(c.phone, '(^[^+]|\(0\))')
   AND c.country = cm.country_code

Пожалуйста, посмотрите демонстрацию SQL Fiddle здесь.

Для UPDATE я бы рекомендовал следующее:

<сильный>1. Создайте временную таблицу на основе приведенного выше запроса:

CREATE TABLE contact_newphone AS
SELECT c.id, c.firstname, c.lastname, c.country, c.phone
     , REGEXP_REPLACE(REGEXP_REPLACE(c.phone, '\(0\)\s*'), '^([^+])', '+' || cm.prefix || ' \1') AS new_phone
  FROM contact c, country_mapping cm
 WHERE REGEXP_LIKE(c.phone, '(^[^+]|\(0\))')
   AND c.country = cm.country_code

<сильный>2. Обновление из этой временной таблицы:

UPDATE contact c
   SET c.phone = ( SELECT cn.newphone FROM contact_newphone cn
                    WHERE cn.id = c.id )
 WHERE REGEXP_LIKE(c.phone, '(^[^+]|\(0\))') -- don't want to update anyone's phone# that might have been fixed!
   AND EXISTS ( SELECT 1 FROM contact_newphone cn
                 WHERE cn.id = c.id )

<сильный>3. Удалите "временную" таблицу или сохраните ее как резервную копию старых, неверных телефонных номеров.

ОБНОВЛЕНИЕ. Если в столбце phone есть начальные пробелы, на шаге 1 можно сделать следующее.

CREATE TABLE contact_newphone AS
SELECT c.id, c.firstname, c.lastname, c.country, c.phone
     , REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(c.phone, '^\s+'), '\(0\)\s*'), '^([^+])', '+' || cm.prefix || ' \1') AS new_phone
  FROM contact c, country_mapping cm
 WHERE REGEXP_LIKE(c.phone, '(^[^+]|\(0\))')
   AND c.country = cm.country_code

Пожалуйста, посмотрите демонстрацию SQL Fiddle здесь. Это также удалит начальные пробелы из телефонных номеров, которые в противном случае хороший.

person David Faber    schedule 23.01.2015
comment
Спасибо, Дэвид, это отличное решение! Один вопрос, чтобы прояснить еще один возможный случай: как следует изменить ваше условие регулярного выражения, чтобы охватить также номера телефонов, начинающиеся с `+ префикс` - где, как вы можете видеть перед отметкой +, у нас может быть пробел или даже пара пробелов? - person Roman; 26.01.2015
comment
Я бы использовал TRIM() для значения c.phone перед применением регулярного выражения для покрытия этого случая. Если вам нужно беспокоиться о других пробельных символах, вы можете использовать для этого REGEXP_REPLACE(). Дайте мне знать, если вам нужна помощь, и я соответствующим образом обновлю свой ответ. - person David Faber; 26.01.2015
comment
Мне очень интересно, как обновить ваш запрос, не могли бы вы? Я играю с \s*, чтобы добавить что-то в выражение регулярного выражения, но пока не удалось. - person Roman; 26.01.2015