Glassdoor (Amazon) — извлечение телефонов из текстовой колонки

Условие

Таблица customer_notes(id, note_text). В свободном тексте встречаются телефоны (+1 (555) 123-4567, 8 800 555 35 35, 555-123-4567). Извлеките все валидные американские номера (10 цифр, опционально +1).

Выход: id, phone (нормализованный к +1XXXXXXXXXX). Один id может дать несколько строк.

Решение

Postgres — REGEXP_MATCHES

SELECT
  id,
  '+1' || REGEXP_REPLACE(
    (regexp_matches(
       note_text,
       '(?:\+?1[\s\-\.]?)?\(?([0-9]{3})\)?[\s\-\.]?([0-9]{3})[\s\-\.]?([0-9]{4})',
       'g'
     ))[1] || (regexp_matches(...))[2] || ...
   ) AS phone
FROM customer_notes;

Чище — через подзапрос, разбив на 3 группы:

SELECT
  cn.id,
  '+1' || arr[1] || arr[2] || arr[3] AS phone
FROM customer_notes cn,
LATERAL regexp_matches(
  cn.note_text,
  '(?:\+?1[\s\-\.]?)?\(?(\d{3})\)?[\s\-\.]?(\d{3})[\s\-\.]?(\d{4})',
  'g'
) AS arr;

LATERAL + regexp_matches('g') возвращает по строке на матч.

MySQL 8+ — REGEXP_SUBSTR

SELECT
  id,
  REGEXP_SUBSTR(note_text, '(\\+?1[\\s\\-]?)?\\(?[0-9]{3}\\)?[\\s\\-]?[0-9]{3}[\\s\\-]?[0-9]{4}') AS phone_raw
FROM customer_notes
WHERE REGEXP_LIKE(note_text, '...');

Чтобы достать все телефоны из одной ячейки, в MySQL придётся через рекурсивный CTE или приложение.

Подводные камни

1. «US-only». Регулярка должна не ловить 8 800 555 35 35 (Россия), +44 ... (UK). ^(?:\+?1)? в начале матча.
2. Ложные срабатывания. 12345 67890 — не телефон. Регэкс с учётом скобок и разделителей фильтрует часть мусора, но идеала нет.
3. Многострочные note_text. regexp_matches('g') обрабатывает по умолчанию весь текст. Если внутри есть \n — норм.
4. Нормализация. Финальное +1XXXXXXXXXX — убираем все non-digit и проверяем длину 10.

Эталонный ответ

LATERAL regexp_matches(text, '<regex>', 'g') для Postgres, склейка трёх групп + префикс +1. На MySQL — REGEXP_SUBSTR (только первое вхождение) + рекурсия для всех.