Поиск по образцу с помощью регулярных выражений

Задача
Вы хотите выполнить не буквальное сравнение, а проверку на соответствие
образцу.

Решение
Используйте оператор REGEXP и регулярные выражения, представленные в дан
ном разделе, или воспользуйтесь шаблоном SQL, описанным в рецепте 4.6.

Обсуждение
Шаблоны SQL присутствуют и в других системах управления базами данных, поэтому они могут быть вынесены за пределы MySQL. Но их возможности ограничены. Например, можно без труда написать такой шаблон SQL, как %abc%, для нахождения строк, содержащих abc, но нельзя создать единый шаблон, который соответствовал бы всем строкам, содержащим любой из символов a, b или c. Невозможно построить образец, который распознавал бы содержимое строки символьного типа, например, буквы это или цифры. Для выполнения таких операций MySQL позволяет применять другой способ поиска по образцу на основе использования регулярных выражений и оператора REGEXP (или NOT REGEXP для инвертирования).1 Операция поиска с использованием регулярных выражений имеет собственный набор специальных символов (табл. 4.4), отличных от % и _ (оба эти символа не имеют специального значения в регулярных выражениях):

Таблица 4.4.


Специальные символы в регулярных выражения
+-----------+-----------------------------------------------------------------------------------+
|Образец | Что соответствует такому образцу |
+-----------+-----------------------------------------------------------------------------------+
| ^ | Начало строки |
| $ | Конец строки |
| . | Любой одиночный символ |
| [...] | Любой символ, приведенный в квадратных скобках |
| [^...] | Любой символ, не приведенный в квадратных |
| p1|p2|p3 | Дизъюнкция; соответствие любому из образцов p1, p2 или p3 |
| * | Ноль или более экземпляров предыдущего элемента |
| + | Один или более экземпляров предыдущего элемента |
| {n} n | экземпляров предыдущего элемента |
| {m,n} | От m до n экземпляров предыдущего элемента |
+-----------+----------------------------------------------------------------------------------+

Символы шаблонов регулярных выражений могут быть вам уже знакомы,так как многие из их используются в vi, grep, sed и других программах UNIX, поддерживающих регулярные выражения.


Большинство из них используется в регулярных выражениях, распознаваемых Perl, PHP и Python. (Например, в главе 10 рассказано о поиске по образцу в сценариях Perl.) Что касается Java, библиотеки классов Jakarta ORO и Regexp содержат функции поиска по образцу, также использующие вышеперечисленные символы.

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

• Строки, начинающиеся с определенной подстроки:
mysql> SELECT name FROM metal WHERE name REGEXP '^co';

+-------- +
| name |
+-------- +
| copper |
+-------- +

• Строки, завершающиеся определенной подстрокой:

mysql> SELECT name FROM metal WHERE name REGEXP 'er$';

+-------- +
| name |
+ --------+
| copper |
| silver |
+-------- +

• Строки, содержащие определенную подстроку:

mysql> SELECT name FROM metal WHERE name REGEXP 'er';

+---------- +
| name |
+---------- +
| copper |
| mercury |
| silver |
+ ----------+

• Строки, содержащие определенную подстроку, начинающуюся с указан
ной позиции:
mysql> SELECT name FROM metal WHERE name REGEXP '^..pp';

+ ---------+
| name |
+--------- +
| copper |
+--------- +

Кроме того, регулярные выражения имеют дополнительные возможности и могут выполнять такие виды поисков, которые недоступны шаблонам SQL.


Например, регулярные выражения могут содержать классы символов, соответствующие любому символу класса:
• Чтобы создать класс символов, перечислите символы, которым должен будет соответствовать класс, в квадратных скобках. Например, образец [abc] соответствует любому из символов a, b или c.
• Классы могут указывать диапазоны символов: задайте начало и конец диапазона и поставьте между ними тире. Образец [a–z] соответствует любой букве, [0–9] – любой цифре, а [a–z0–9] соответствует и буквам, и цифрам.
• Чтобы инвертировать класс символов (задать соответствие любому символу, кроме указанных в классе), предварите список символом ^. Например, [^0–9] соответствует любым символам, кроме цифр.

Регулярные выражения MySQL также поддерживают классы символов
POSIX. Эти классы соответствуют специальным наборам символов (табл. 4.5).

Таблица 4.5. Классы символов POSIX

Класс POSIX | Чему соответствует класс
-----------------+-----------------------------------------------------------------
[:alnum:] | Буквенные и цифровые символы
[:alpha:] | Буквенные символы
[:blank:] | Пробельные символы (пробел или знак табуляции)
[:cntrl:] | Управляющие символы
[:digit:] | Цифры
[:graph:] | Графические символы (не пробельные)
[:lower:] | Буквенные символы в нижнем регистре
[:print:] | Графические символы или пробел
[:punct:] | Знаки пунктуации
[:space:] | Пробел, табуляция, новая строка, возврат каретки
[:upper:] | Буквенные символы в верхнем регистре
[:xdigit:] | Шестнадцатеричные цифры (0–9, a–f, A–F)

Классы POSIX предназначены для использования в классах символов, так что заключайте их в квадратные скобки.


Следующее выражение соответствует значениям, которые могут содержать любые символы шестнадцатеричных цифр:

mysql> SELECT name, name REGEXP '[[:xdigit:]]' FROM metal;

+------------ +------------------------------- +
| name | name REGEXP '[[:xdigit:]]' |
+ ------------+------------------------------- +
| copper | 1 |
| gold | 1 |
| iron | 0 |
| lead | 1 |
| mercury | 1 |
| platinum | 1 |
| silver | 1 |
| tin | 0 |
+----------- + -------------------------------+

Регулярные выражения могут содержать дизъюнкцию:

выбор1|выбор2|...

Дизъюнкция похожа на класс символов – она соответствует любому из вариантов.


Но в отличие от класса символов, дизъюнкция может включать не только отдельные символы, но и строки, и даже образцы. Например, следующая дизъюнкция соответствует строкам, которые начинаются с гласной или заканчиваются на er:

mysql> SELECT name FROM metal WHERE name REGEXP '^[aeiou]|er$';

+--------- +
| name |
+--------- +
| copper |
| iron |
| silver |
+--------- +

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

mysql> SELECT '0m' REGEXP '^[[:digit:]]+|[[:alpha:]]+$';

+ -------------------------------------------------+
| '0m' REGEXP '^[[:digit:]]+|[[:alpha:]]+$' |
+ -------------------------------------------------+
| 1 |
+ -------------------------------------------------+

Как видно из результата запроса, поиск по образцу не работает. Дело в том, что ^ применяется к первому варианту, а $ – ко второму. Так что на самом деле образец соответствует строкам, которые начинаются с одной или нескольких цифр, или строкам, которые заканчиваются одной или несколькими буквами. А вот если заключить дизъюнкцию в скобки, то ^ и $ будут применены к обоим вариантам, и образец будет работать, как и ожидалось:

mysql> SELECT '0m' REGEXP '^([[:digit:]]+|[[:alpha:]]+)$';

+--------------------------------------------------- +
| '0m' REGEXP '^([[:digit:]]+|[[:alpha:]]+)$' |
+ ---------------------------------------------------+
| 0 |
+ ---------------------------------------------------+

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

'abc' REGEXP 'b'
'abc' REGEXP '^.*b.*$'

Регулярные выражения не соответствуют значениям NULL. Это относится и к REGEXP, и к NOT REGEXP:

mysql> SELECT NULL REGEXP '.*', NULL NOT REGEXP '.*';

+--------------------- + ---------------------------+
| NULL REGEXP '.*' | NULL NOT REGEXP '.*' |
+--------------------- +--------------------------- +
| NULL | NULL |
+--------------------- +--------------------------- +

Так как регулярное выражение соответствует строке, если образец найден в любой ее части, необходимо следить за тем, чтобы случайно не задать образец, соответствующий пустой строке. Если вы укажете такой образец, он будет соответствовать любым значениям, отличным от NULL. Например,образец a* соответствует любому количеству символов a, даже нулевому. Если вашей целью является получение только строк, содержащих непустые последовательности символов a, используйте a+. Такой образец (с +) требует вхождения в строку одного или более экземпляров указанного элемента.
Аналогично поиску по шаблонам SQL при помощи LIKE, поиск при помощи регулярных выражений в некоторых случаях эквивалентен поиску подстрок. Метасимволы ^ и $ действуют подобно LEFT() и RIGHT(), по крайней мере, если речь идет о буквенных строках (табл. 4.6):

Таблица 4.6. Аналогичные операции
+----------------------+--------------------------+
| Поиск по образцу | Поиск подстроки |
+----------------------+--------------------------+
| str REGEXP '^abc' | LEFT(str,3) = 'abc' |
| str REGEXP 'abc$' | RIGHT(str,3) = 'abc' |
+----------------------+--------------------------+

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

str REGEXP '^[0 9]+'
Функция LEFT() этого не умеет (как и LIKE, кстати).

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
FULLTEXT поиск и короткие слова
Буквальная интерпретация метасимволов в шаблонах
Включение и исключение слов из FULLTEXT - поиска
Поиск по образцу с помощью шаблонов SQL
Поиск с помощью индекса FULLTEXT

Вернуться в раздел: MySQL / 4. Работа со строками