and regexp like(

regexp replace(text,

[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4},***), [0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4})

EMP ID TEXT

7369 126 Varnum, Edmore MI 48829, 989 313-5351 7844 989-387.5359

9999 906-387-1698, 313-535.8886

Каждая из этих строк содержит, по крайней мере, один телефонный номер, отформатированный неправильно.

Обсуждение

Ключ к этому решению в определении истинного телефонного номера . Поскольку телефонные номера хранятся в поле комментариев, любой текст в поле может быть истолкован как недействительный телефонный номер. Необходимо сузить рассматриваемое множество до более разумного набора значений. Например, не хотелось бы видеть в выводе следующую строку:

EMP ID TEXT

7900 Cares for 100-year-old aunt during the day. Schedule only for evening and night shifts.

Понятно, что в этой строке вообще нет никакого телефонного номера, тем более неверно форматированного. Для нас это очевидно. Вопрос в том, как заставить СУБД увидеть это. Думаю, ответ вам понравится. Пожалуйста, дочитайте до конца.

Этот рецепт взят из статьи Джонатана Генника (по его разрешению) Regular Expression Anti-Patterns , которую можно найти по адресу: http: gennick.com/antiregex.htm.

Для определения множества истинных телефонных номеров в решении используется Pattern A (шаблон А):

Pattern A: [0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}

Pattern A проверяет наличие двух групп, состоящих из трех цифр, за которыми следует группа из четырех цифр. В качестве разделителей между группами могут выступать тире (-), точка (.) или пробел. Можно было бы создать более сложный шаблон, например принять во внимание и семизначные телефонные номера. Но не будем уходить в сторону. Наша задача состоит в том, что мы должны каким-то образом определить множество возможных телефонных номеров. В данном случае это множество определено Pattern A. Можно использовать другой Pattern A, но решение в целом будет по-прежнему работоспособным.

В данном решении Pattern A размещается в предикате WHERE, что гарантирует рассмотрение только тех строк, в которых потенциально присутствуют телефонные номера (согласно шаблону!):

select emp id, text

from employee comment where regexp like(text, [0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4})

Далее необходимо определить, как выглядит действительный телефонный номер. В решении для этого применяется Pattern B:

Pattern B: [0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}

На этот раз в шаблоне используется \1, что указывает на первое подвыражение. Если символ соответствует ([-. ]), он также должен соответствовать \1. Pattern B описывает действительные телефонные номера, которые должны быть исключены из рассмотрения (поскольку они не являются ошибочными). В решении исключение правильно форматированных телефонных номеров осуществляется через вызов функции REGEXPREPLACE:

regexp replace(text,

[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4},...),

Вызов REGEXP REPLACE выполняется в предикате WHERE. Все

правильно форматированные телефонные номера замещаются строкой из трех звездочек. Опять же Pattern B может быть любым, главное, чтобы он описывал допустимый формат номеров.

После того как все правильно форматированные телефонные номера замещены строками из трех звездочек (***), все оставшиеся истинные телефонные номера должны быть по определению неправильно отформатированными. Применяя функцию REGEXPLIKE к выводу функции REGEXP REPLACE, проверяем, остались ли неправильно форматированные номера:

and regexp like(

regexp replace(text,

[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4},...), [0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4})

Этот рецепт было бы тяжело реализовать, если бы не возможности сопоставления шаблонов, возникшие относительно недавно в Oracle с появлением регулярных выражений. В частности, этот рецепт полагается на REGEXPREPLACE. Другие базы данных (например, PostgreSQL) реализуют поддержку регулярных выражений. Но, насколько мне известно, только Oracle поддерживает функциональность поиска и замены на основании регулярных выражений, на которых построено данное решение.

Преобразование данных с помощью вложенного запроса

Задача

Имеется таблица, в одном из столбцов которой могут храниться как числовые, так и символьные данные. В этой же таблице есть другой столбец, показывающий, какие именно данные хранятся в первом столбце. Требуется с помощью подзапроса выбрать только числовые данные:

select *

from ( select flag, to number(num) num from subtest

where flag in (A, C) ) where num > 0

К сожалению, выполнение этого запроса часто (но не всегда!) приводит к следующему сообщению об ошибке:

ERROR:

ORA-01722: invalid number

Решение

Одно из решений - заставить вложенный запрос полностью завершиться до выполнения внешнего выражения SELECT. Это можно сделать, по крайней мере, в Oracle, включая псевдостолбец номера строки в список внутреннего SELECT:

select *

from ( select rownum, flag, to number(num) num from subtest

where flag in (A, C) ) where num > 0

В разделе Обсуждение объясняется, почему это решение работает.

Обсуждение

Причина возникновения ошибки в запросе, рассматриваемом в разделе Задача , в том, что некоторые оптимизаторы объединяют внутренний и внешний запросы. Хотя все выглядит так, будто сначала выполняется внутренний запрос, удаляющий все нечисловые значения NUM, на самом деле происходит следующее:

select flag, to number(num) num from subtest

where to number(num) > 0 and flag in (A, C);

Вот теперь причина ошибки очевидна: строки с нечисловыми значениями в поле NUM не отсеиваются перед применением функции

TO NUMBER.