MySQL regexp регулярные выражения в mysql


REGEXP в MySQL

SELECT * FROM prices where tkey REGEXP ‘^toy’;
Вернет все записи где текст в колонке tkey начинается с букв “toy”.
SELECT * FROM prices where tkey REGEXP ‘[[:digit:]]’;
Вернет все записи где в колонке tkey только цифры.
^ — означает начало слова
$ — конец
. — Соответствие любому символу (включая перевод строки).
a* — Соответствие любой последовательности из нуля или более символов «a».
a+ — Соответствие любой последовательности из одного или более символов «a».
a? — Соответствие как нулю, так и одному символу «a».
de|abc — Соответствие как последовательности de, так и последовательности abc.
(abc)* — Соответствие нулю или более вхождениям последовательности abc.
[а-ЯЁё] — в слове могут быть буквы только Русского алфавита.
[а-ЯЁёa-Z] — русскаго и англикого

MySQL regexp: регулярные выражения в mysql

Поговорим о регулярных выражениях в mysql. Или как сейчас принято называть mysqlregexp. БД MySQL имеет множество инструментов для реализации поиска информации. Это можно сделать с помощью оператора like или =, или regexp, о чем мы и поговорим.

Регулярное выражение это набор символов, определяющий шаблон строки. По этому шаблону и происходит поиск нужных данных в БД MySQL. Например, если регулярное выражение соответствует sitear, то результатом будут совпадения с sitear и ничего больше. Более сложное это sitear|sitearchitector, будет находить совпадения как по sitear, так и по sitearchitector.

Регулярные выражения, усовершенствующие SQL-предложения

Регулярные выражения в Oracle Database 10g — это мощный инструмент для манипулирования текстовыми данными.

Новая возможность Oracle Database 10g значительно увеличивает способность поиска и манипулирования символьными данными. Эта возможность, регулярные выражения, это транскрипция для описания образца текста. Она давно имеется во многих языках программирования и множестве утилит для UNIX.

Регулярные выражения в Oracle реализованы в виде SQL-функций и оператора выражения WHERE. Тем, кто еще ничего не знает о регулярных выражениях, эта статья может дать общее понятие об этой новой и весьма мощной, но пока кажущейся непонятной, возможности. Читатели, которые уже познакомились с регулярными выражениями, могут пополнить знания о том, как применить эту функциональность в контексте языка Oracle SQL.

Что такое регулярное выражение?

Регулярное выражение содержит один и более символов и/или метасимволов. В самом простом виде регулярное выражение может состоять только из символов, например, регулярное выражение cat. Оно читается как буква c, за которой следует буква a и t, и этому шаблону соответствуют такие строки, как cat, location и catalog. Метасимволы обеспечивают алгоритмы обработки в Oracle символов, из которых состоит регулярное выражение. Когда значение различных метасимволов будет понятным, вы увидите, как удобны регулярные выражения для выделения и замены каких-либо текстовых данных.

Проверка данных, поиск дубликатов, обнаружение лишних пробелов или разбор строки — это некоторые из многих примеров использования регулярных выражений. Их можно применять для проверки формата телефонного номера, zip-кода, email-адресов, номеров социального обеспечения, IP-адресов, имен файлов и директорий и так далее. Кроме того, можно искать комбинации, например, HTML-тегов, чисел, дат, и другое, которые соответствуют какому-либо шаблону в тексте, и заменять их другим набором символов.

Использование регулярных выражений в Oracle Database 10g

Чтобы воспользоваться возможностями регулярных выражений, можно применить функции REGEXP_INSTR, REGEXP_SUBSTR и REGEXP_REPLACE и новый оператор Oracle SQL — REGEXP_LIKE. Вы увидите, как эта новая функциональность поддерживает существующий оператор LIKE и функции INSTR, SUBSTR и REPLACE. Они действительно похожи на существующие оператор и функции, однако теперь предоставляются мощные возможности сопоставления с шаблоном. Искомые данные могут быть простой строкой или текстом большого объема, хранимым в символьном столбце базы данных. Регулярные выражения позволяют искать, заменять и проверять данные способом, о котором ранее и не мечтали, с высокой степенью гибкости.

Примеры регулярных выражений

Перед использованием новой функциональности необходимо понять значение некоторых метасимволов. Период (.) в регулярном выражении соответствует любому символу (за исключением перехода на новую строку). Например, регулярное выражение a.b соответствует строке, содержащей букву a, за которой следует один любой символ (за исключением перехода на новую строку), за которым следует буква b. Строки axb, xaybx и abba подходят, так как содержат этот шаблон. Если требуется точное соответствие трехсимвольной строке, в которой строка начинается с a и заканчивается b, регулярное выражение необходимо привязать. Метасимвол вставки (^) обозначает начало строки, а доллар ($) — конец строки (см. Таблицу 1). Поэтому регулярному выражению ^a.b$ соответствуют строки aab, abb или axb. Чтобы сравнить этот метод со знакомым сопоставлением с шаблоном оператора LIKE, можно использовать такой шаблон как a_b, где подчеркивание (_) — это любой одиночный символ.

По умолчанию отдельный символ или список символов регулярного выражения сопоставляются один раз. Чтобы найти несколько вхождений символа регулярного выражения, применяется квантификатор, называемый также оператором повтора. Если требуется соответствие строке, которая начинается с буквы a и заканчивается буквой b, регулярное выражение выглядит следующим образом: ^a.*b$. Метасимвол * повторяет предыдущее соответствие любому метасимволу (.) ноль, один и более раз. Эквивалентный шаблон оператора LIKE — это a%b, где (%) обозначает ноль, одно и более вхождений любых символов.

В Таблице 2 показан полный список операторов повтора. Заметьте, что в ней содержатся специфичные варианты повтора, которые допускают больше гибкости, чем существующие групповые символы оператора LIKE. Если выражение заключить в скобки, в результате чего образуется подвыражение, то подвыражение может повторяться заданное число раз. Например, регулярному выражению b(an)*a соответствует ba, bana, banana, yourbananasplit, и так далее.

Регулярные выражения Oracle поддерживают символьные классы POSIX (Portable Operating System Interface), которые перечислены в Таблице 3. Это значит, что можно искать совершенно особые типы символов. Представьте написание условия с оператором LIKE, которое ищет только символы, не являющиеся буквами — получающееся выражение WHERE легко становится очень сложным.

Символьный класс POSIX должен входить в список символов, обозначаемый квадратными скобками ([]). Например, регулярное выражение [[:lower:]] соответствует одному символу в нижнем регистре, а [[:lower:]] <5>— пяти последовательным символам в нижнем регистре.

Кроме символьных классов POSIX, в список символов можно включать отдельные символы. Например, регулярное выражение ^ab[cd]ef$ соответствует строкам abcef и abdef. Сопоставляются как c, так и d.

Большинство метасимволов списка понимаются как литеры, за исключением вставки (^) и дефиса (-). Регулярные выражения могут казаться сложными, так как одни и те же метасимволы имеют несколько значений в зависимости от контекста. ^ как раз один из таких метасимволов. Если он — первый символ в списке символов, то означает не вхождение в список. Поэтому, [^[:digit:]] ищет соответствие, состоящее только из любых нецифровых символов, в то время как ^[[:digit:]] ищет соответствие, которое начинается с цифры. Дефис (-) обозначает диапазон; регулярное выражение [a-m] соответствует любым буквам от a до m. Однако он является литерой дефис, если находится в начале списка символов, например [-afg].

Один из предыдущих примеров показывал использование скобок для создания подвыражения; они позволяют ввести альтернативы, разделенные вертикальной чертой (/) — метасимволом альтернативы.

Например, регулярное выражение t(a/e/i)n допускает три возможных альтернативных символа между буквами t и n. Этому шаблону соответствуют слова tan, ten, tin и Pakistan, но не teen, mountain или tune. С другой стороны, регулярное выражение t(a/e/i)n можно описать как список символов, например t[aei]n. Эти метасимволы сгруппированы в Таблице 4. Хотя метасимволов значительно больше, этот краткий список важен для понимания регулярных выражений, используемых в настоящей статье.

Когда в базе данных Oracle встречается оператор REGEXP_LIKE, он знакомит с регулярными выражениями. В таблице 5 показан синтаксис REGEXP_LIKE.

Следующий SQL-запрос с выражением WHERE демонстрирует оператор REGEXP_LIKE, который ищет столбец ZIP со значениями, которые удовлетворяют регулярному выражению [^[:digit:]]. Отбираться будут те строки таблицы ZIPCODE, в которых значение столбца ZIP содержит любой символ, не являющийся цифрой.

Этот пример регулярного выражения состоит только из метасимволов, а точнее, из символьного класса POSIX digit, ограниченного двоеточиями и квадратными скобками. Второй набор скобок (как в [^[:digit:]]) ограничивает список символьных классов. Как пояснялось выше, это необходимо, так как символьные классы POSIX могут использоваться только для формирования списка символов.

Эта функция возвращает начальную позицию образца, поэтому она работает примерно так, как уже знакомая функция INSTR. Синтаксис новой функции REGEXP_INSTR показан в Таблице 6. Основное различие между этими двумя функциями заключается в том, что REGEXP_INSTR позволяет указать шаблон вместо конкретной строки поиска; обеспечивая, таким образом, большее многообразие. Следующий пример использует функцию REGEXP_INSTR, которая возвращает начальную позицию пятицифрового zip-кода в строке Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. Если регулярное выражение выглядит как [[:digit:]]<5>, то вместо zip-кода возвратится начальная позиция номера дома, так как 10045 — это первое вхождение из пяти последовательных цифр. Поэтому выражение необходимо привязать к концу строки метасимволом $, тогда функция отобразит начальную позицию zip-кода вместо набора цифр, соответствующих номеру дома.


Более сложные шаблоны

Давайте усложним шаблон zip-кода предыдущего примера, чтобы найти дополнительные четыре цифры. Теперь он может выглядеть так: [[:digit:]]<5>(-[[:digit:]]<4>)?$. Если исходная строка заканчивается пятицифровым zip-кодом или 5-цифровым + 4 zip-кодом, то надо будет показать начальную позицию шаблона.

В этом примере в скобки заключено подвыражение (-[[:digit:]]<4>), которое может повторяться ноль и более раз, что показывает оператор повтора? С другой стороны, если попытаться использовать традиционные SQL-функции для получения того же результата, то формулировка будет сложна даже для знатока SQL. Чтобы лучше объяснить различные компоненты этого примера регулярного выражения, в Таблице 7 представлено описание отдельных литер и метасимволов.

Функция REGEXP_SUBSTR, очень похожа на функцию SUBSTR и возвращает часть строки. В Таблице 8 показан синтаксис новой функции. В следующем примере возвращается строка, которая соответствует шаблону [^,]*. Регулярное выражение ищет запятую, за которой следует пробел; затем ноль и более символов, не являющихся запятыми, что показывает [^,]*; и затем ищет другую запятую. Шаблон будет выглядеть примерно как строка значений, разделенных запятыми.

Сначала рассмотрим традиционную SQL-функцию REPLACE, которая заменяет одну строку другой. Предположим, что в тексте имеются дополнительные пробелы, а вам хотелось бы заменить их одним пробелом. В функции REPLACE необходимо перечислить ровно столько пробелов, сколько надо заменить. Однако дополнительные пробелы могут располагаться не только в одном месте. Следующий пример имеет три пробела между Joe и Smith. Параметр функции REPLACE показывает, что два пробела следует заменить одним пробелом. В этом случае результат имеет на один дополнительный пробел меньше, в то время как в исходной строке между Joe и Smith было три пробела.

Функция REGEXP_REPLACE выполняет замены на порядок дальше; она описана в Таблице 9. Следующий запрос заменяет два и более любых пробелов на один пробел. Подвыражение ( ) содержит один пробел, который может повторяться два и более раз, что показывает <2,>.

Полезная возможность регулярных выражений — это способность запоминать подвыражение для дальнейшего использования; она называется также ссылочность (описана в Таблице 10). Она позволяет выполнять сложные замены, такие как перемещение образца на новую позицию или нахождение повторяющегося слова или буквы. Соответствующая подвыражению часть сохраняется во временном буфере. Буферы нумеруются слева направо, а доступ к ним осуществляется через \цифра, где цифра, это число от 1 до 9, которое соответствует порядковому номеру подвыражения, обозначенному скобками.

Следующий пример показывает, как имя Ellen Hildi Smith преобразовывается в Smith, Ellen Hildi с помощью ссылок на отдельные подвыражения по номеру.

В этом SQL-предложении есть три отдельных подвыражения, заключенных в скобки. Каждое отдельное подвыражение содержит метасимвол соответствия любому символу (.), за которым следует метасимвол *, показывающий, что любой символ (за исключением перехода на новую строку) должен появиться ноль и более раз. Каждое подвыражение разделяет пробел, который также должен быть сопоставлен. Скобки обозначают подвыражения для фиксации значений, на которые можно ссылаться через \цифра. Первое подвыражение связывается с \1, второе \2 и так далее. Эти ссылки используются в последнем параметре этой функции (\3, \1 \2), которая возвращает измененную подстроку, преобразовав ее в требуемый формат (включая запятую и пробелы). В Таблице 11 подробно описываются отдельные элементы этого регулярного выражения.

Ссылки полезны для замены, форматирования и нахождения подстроки, и могут применяться для поиска расположенных рядом значений. Следующий пример показывает использование функции REGEXP_SUBSTR для поиска дубликатов символьно-цифровых значений, разделенных пробелом. Полученный результат показывает слова, которые продублированы.

Дополнительный параметр сопоставления

Вы, возможно, заметили, что оператор и функции регулярного выражения могут содержать дополнительный параметр сопоставления. Этот параметр управляет учетом регистра, переходом на новую строку и многострочными вхождениями повторов.

Практические приложения регулярных выражений

Регулярные выражения используются не только в запросах, но также в любом месте, где допускаются SQL-операторы и функции, например в PL/SQL. Можно написать триггеры, в которых регулярные выражения полезно использовать для проверки, генерации или выделения значений.

Следующий пример показывает, как оператор REGEXP_LIKE можно применять в check-ограничении на столбец для контроля данных. Он проверяет корректность формата номера социального обеспечения при вставке или изменении записи. Для такого ограничения столбца допустим номер социального обеспечения в формате 123-45-6789 или 123456789. Корректные данные должны начинаться с трех цифр, за которыми следует дефис, две или более цифр, дефис и, наконец, другие четыре цифры. Альтернативное выражение допускает только девять последовательных цифр. Варианты разделяются вертикальной чертой (/).

Символы перед или после не допускаются, что показывают ^ и $. Необходимо убедиться, что регулярное выражение не разбито на несколько строк и не содержит лишних пробелов, если только не требуется, чтобы они были частью шаблона и поэтому сопоставлялись. В Таблице 12 объясняются отдельные элементы этого примера регулярного выражения.

Смотрите на странице the Oracle Database 10g:

Сравнение регулярных выражений с существующей функциональностью

Илон Маск рекомендует:  Что такое код imap_scanmailbox

Регулярные выражения имеют некоторые преимущества перед обычным оператором LIKE и функциями INSTR, SUBSTR и REPLACE. Эти традиционные SQL-функции не имеют возможности сопоставления с шаблоном. Только оператор LIKE может выполнять символьное сопоставление с помощью символов группировки % и _, однако LIKE не поддерживает повторы выражения, сложное чередование, диапазоны символов, списки символов, символьные классы POSIX, и др. А новые функции с регулярными выражениями позволяют найти еще и дубликаты, и выполнить перестановку. Примеры, показанные в этой статье, позволяют кратко заглянуть в мир регулярных выражений и понять, как применять их в приложениях.

Полезное добавление к инструментарию

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

Алиса Ришет ( ar280@yahoo.com ) работает в Database Application Development и Design track в Columbia University’s Computer Technology and Application Program. Она автор Oracle SQL Interactive Workbook 2nd edition (Prentice Hall, 2002) и готовящейся в публикации Oracle SQL by Example (Prentice Hall, 2003). Ришет — архитектор баз данных, DBA и руководитель проекта с 15-летним стажем для Fortune 100 компаний и работает с Oracle, начиная с 5-ой версии.

Таблица 1: Метасимволы привязки

Метасимвол Описание
Привязать выражение к началу строки
$ Привязать выражение к концу строки

Таблица 2: Квантификаторы и операторы повтора

Квантификатор Описание
* Встречается 0 и более раз
? Встречается 0 или 1 раз
+ Встречается 1 и более раз
Встречается ровно m раз
Встречается по крайней мере m раз
Встречается по крайней мере m раз, но не более n раз

Таблица 3: Предопределенные символьные классы POSIX

Буквы в верхнем регистре


Класс символов Описание
[:alpha:] Буквы
[:lower:] Буквы в нижнем регистре
[:upper:]
[:digit:] Цифры
[:alnum:] Буквы и цифры
[:space:] Пробелы (не печатаемые символы), такие как перевод каретки, новая строка, вертикальная табуляция и подача страницы
[:punct:]
[:cntrl:] Управляющие символы (не печатаемые)
[:print:] Печатаемые символы

Таблица 4: Альтернативное сопоставление и группировка выражений

Метасимвол Описание
/ Альтернатива Разделяет альтернативные варианты, часто используется с оператором группировки ()
( ) Группа Группирует подвыражения для альтернативы, квантификатора или ссылочности (см. раздел «Ссылки»)
[char] Список символов Обозначает список символов; большинство метасимволов в списке символов представляют собой литеры, за исключением символьных классов и метасимволов ^ и —

Таблица 5: Оператор REGEXP_LIKE

Синтаксис Описание
REGEXP_LIKE(исходная_строка, шаблон[, параметр_сопоставления]) «исходная_строка» поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG). Параметр «шаблон» — это другое название регулярного выражения. «параметр_сопоставления» позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра.

Таблица 6: Функция REGEXP_INSTR

Синтаксис Описание
REGEXP_INSTR(исходная_строка, шаблон
[, начальная_позиция
[, вхождение
[, опция_возврата
[, параметр_сопоставления]]]])
Эта функция ищет по шаблону и возвращает первую позицию. Дополнительно можно указать параметр «начальная_позиция», с которой должен начинаться поиск. Параметр «вхождение» по умолчанию имеет значение 1, если пользователь не укажет поиск последовательных вхождений. Значение по умолчанию для параметра «опция_возврата» — это 0, тогда возвратится начальная позиция шаблона; при значении 1 возвращается позиция символа, следующего за шаблоном.

Таблица 7: Описание выражения для 5-цифрового + 4 Zip-кода

Синтаксис Описание
Пробел, который должен быть сопоставлен
[:digit:] Числовой класс POSIX
] Конец списка символов
Повторять список символов ровно пять раз
( Начало подвыражения
Литера дефис, так как он не является метасимволом диапазона внутри списка символов
[ Начало списка символов
[:digit:] Класс POSIX [:digit:]
[ Начало списка символов
] Конец списка символов
Повторять список символов ровно четыре раза
) Закрывающая скобка — конец подвыражения
? Квантификатор ? относится к групповому подвыражению 0 или 1 раз, в результате чего 4-цифровой код становится дополнительным
$ Метасимвол привязки, чтобы показать конец строки

Таблица 8: Функция REGEXP_SUBSTR

Синтаксис Описание
REGEXP_SUBSTR(исходная_строка, шаблон
[, позиция [, вхождение
[,параметр_сопоставления]]])
Функция REGEXP_SUBSTR возвращает подстроку, которая соответствует шаблону.

Таблица 9: Функция REGEXP_REPLACE

Синтаксис Описание
REGEXP_REPLACE(исходная_строка, шаблон
[, строка_замены [, позиция
[,вхождение, [параметр_сопоставления]]]])
Эта функция заменяет подстроку, соответствующую шаблону на заданную «строку_замены», используя сложные операции поиска-и-замены.

Таблица 10: Метасимвол ссылки

Метасимвол Описание
\digit Обратная косая черта За ней следует цифра от 1 до 9, обратная косая черта связана с предыдущим сопоставлением с соответствующим номером заключенного в скобки подвыражения.

(Заметьте: Обратная косая черта может иметь другое значение в регулярном выражении; в зависимости от контекста она может означать также символ Escape

Поиск и замена в PL/SQL с использованием регулярных выражений

В Oracle10g в области работы со строками произошли очень серьезные изменения: была реализована поддержка регулярных выражений. Причем речь идет не об упрощенной поддержке регулярных выражений вроде предиката LIKE, которая встречается в других СУБД. Компания Oracle предоставила в наше распоряжение отлично проработанный, мощный набор функций — то, что было необходимо в PL/SQL.

Регулярные выражения образуют своего рода язык для описания и обработки текста. Читатели, знакомые с языком Perl , уже разбираются в этой теме, поскольку Perl способствовал распространению регулярных выражений в большей степени, чем любой другой язык. Поддержка регулярных выражений в Oracle10g довольно близко соответствовала стандарту регулярных выражений POSIX (Portable Operating System Interface). В Oracle10g Release 2 появилась поддержка многих нестандартных, но весьма полезных операторов из мира Perl , а в Oracle11g возможности регулярных выражений были дополнительно расширены.

Проверка наличия совпадения

Регулярные выражения используются для описания текста, который требуется найти в строке (и возможно, подвергнуть дополнительной обработке). Давайте вернемся к примеру, который приводился ранее в этом блоге:

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

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

Здесь исходная_строка — символьная строка, в которой ищутся совпадения; шаблон — регулярное выражение, совпадения которого ищутся в исходной_строке; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Если функция REGEXP_LIKE находит совпадение шаблона в исходной_строке , она возвращает логическое значение TRUE ; в противном случае возвращается FALSE .

Процесс построения регулярного выражения выглядел примерно так:

  • [a-z A-Z] Каждый элемент списка имен может состоять только из букв и пробелов. Квадратные скобки определяют набор символов, которые могут входить в совпадение. Диапазон a–z описывает все буквы нижнего регистра, а диапазон A–Z — все буквы верхнего регистра. Пробел находится между двумя компонентами выражения. Таким образом, этот шаблон описывает один любой символ нижнего или верхнего регистра или пробел.
  • [a-z A-Z] * Звездочка является квантификатором — служебным символом, который указывает, что каждый элемент списка содержит ноль или более повторений совпадения, описанного шаблоном в квадратных скобках.
  • [a-z A-Z] *, Каждый элемент списка должен завершаться запятой. Последний элемент является исключением, но пока мы не будем обращать внимания на эту подробность.
  • ([a-z A-Z] *,) Круглые скобки определяют подвыражение, которое описывает некоторое количество символов, завершаемых запятой. Мы определяем это подвыражение, потому что оно должно повторяться при поиске.
  • ([a-z A-Z ]*,)+ Знак + — еще один квантификатор, применяемый к предшествующему элементу (то есть к подвыражению в круглых скобках). В отличие от * знак + означает «одно или более повторений». Список, разделенный запятыми, состоит из одного или нескольких повторений подвыражения.
  • ( [a-z A-Z]*,)+([a-z A-Z]*) В шаблон добавляется еще одно подвыражение: ( [a-z A-Z] *). Оно почти совпадает с первым, но не содержит запятой. Последний элемент списка не завершается запятой.
  • ([a-z A-Z]*,)+([a-z A-Z]*) <1>Мы добавляем квантификатор <1>, чтобы разрешить вхождение ровно одного элемента списка без завершающей запятой.
  • ^ ([a-z A-Z]*,)+([a-z A-Z]*)<1>$ Наконец, метасимволы ^ и $ привязывают потенциальное совпадение к началу и концу целевой строки. Это означает, что совпадением шаблона может быть только вся строка вместо некоторого подмножества ее символов.

Функция REGEXP_LIKE анализирует список имен и проверяет, соответствует ли он шаблону. Эта функция оптимизирована для простого обнаружения совпадения шаблона в строке, но другие функции способны на большее!

Поиск совпадения

Функция REGEXP_INSTR используется для поиска совпадений шаблона в строке. Общий синтаксис REGEXP_INSTR :

Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); флаг_возвращаемого_значения — 0 для начальной позиции или 1 для конечной позиции совпадения; модификаторы — один или несколько модификаторов, управляющих процессом поиска (например, i для поиска без учета регистра). Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция REGEXP_INST возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки).

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

При выполнении этого фрагмента выясняется, что имя на букву A, завершающееся согласной (Andrew), начинается в позиции 22. А вот как проходило построение шаблона:


  • A Совпадение начинается с буквы A. Беспокоиться о запятых не нужно — на этой стадии мы уже знаем, что работаем со списком, разделенным запятыми.
  • A[a-z ]* За буквой A следует некоторое количество букв или пробелов. Квантификатор * указывает, что за буквой A следует ноль или более таких символов.
  • A[a-z ]*[^aeiou] В выражение включается компонент [ ^aeiou ], чтобы имя могло заканчиваться любым символом, кроме гласной. Знак ^ инвертирует содержимое квадратных скобок —
    совпадает любой символ, кроме гласной буквы. Так как квантификатор не указан, требуется присутствие ровно одного такого символа.
  • A[a-z ]*[^aeiou], Совпадение должно завершаться запятой; в противном случае шаблон найдет совпадение для подстроки «An» в имени «Anna». Хотя добавление запятой решает эту проблему, тут же возникает другая: шаблон не найдет совпадение для имени «Aaron» в конце строки.
  • A[a-z ]*[^aeiou],|A[a-z ]*[^aeiou]$ В выражении появляется вертикальная черта (|), обозначение альтернативы: общее совпадение находится при совпадении любого из вариантов. Первый вариант завершается запятой, второй — нет. Второй вариант учитывает возможность того, что текущее имя стоит на последнем месте в списке, поэтому он привязывается к концу строки метасимволом $ .

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

Функция REGEXP_INSTR приносит пользу в некоторых ситуациях, но обычно нас больше интересует текст совпадения, а не информация о его положении в строке.

Получение текста совпадения

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

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

Результат работы программы:

Ого! Шаблон получился довольно устрашающим. Давайте разобьем его на составляющие:

  • \(? Шаблон начинается с необязательного символа открывающей круглой скобки. Так как круглые скобки в языке регулярных выражений являются метасимволами (то есть имеют специальное значение), для использования в качестве литерала символ круглой скобки необходимо экранировать ( escape ), поставив перед ним символ \ (обратная косая черта). Вопросительный знак — квантификатор, обозначающий ноль или одно вхождение предшествующего символа. Таким образом, эта часть выражения описывает необязательный символ открывающей круглой скобки.
  • \d <3>\d — один из операторов, появившихся в Oracle10g Release 2 под влиянием языка Perl. Он обозначает произвольную цифру. Квантификатор <> указывает, что предшествующий символ входит в шаблон заданное количество раз (в данном случае три). Эта часть шаблона описывает три цифры.
  • \)? Необязательный символ закрывающей круглой скобки.
  • [[:space:]\.\-]? В квадратных скобках перечисляются символы, для которых обнаруживается совпадение — в данном случае это пропуск, точка или дефис. Конструкция [ :space: ] обозначает символьный класс POSIX для пропускных символов (пробел, табуляция, новая строка) в текущем наборе NLS. Точка и дефис являются метасимволами, поэтому в шаблоне их необходимо экранировать обратной косой чертой. Наконец, ? означает ноль или одно вхождение предшествующего символа. Эта часть шаблона описывает необязательный пропуск, точку или дефис.
  • \d <3>Эта часть шаблона описывает три цифры (см. выше).
  • [[:space:]\.\-]? Эта часть шаблона описывает необязательный пропуск, точку или дефис (см. выше).
  • \d <4>Четыре цифры (см. выше).

Обязательно комментируйте свой код, использующий регулярные выражения, — это пригодится тому, кто будет разбираться в нем (вполне возможно, это будете вы сами через полгода).

Общий синтаксис REGEXP_SUBSTR :

Функция REGEXP_SUBSTR возвращает часть исходной_строки, совпадающую с шаблоном или подвыражением. Если совпадение не обнаружено, функция возвращает NULL . Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке ; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); модификаторы — один или несколько модификаторов, управляющих процессом поиска.

Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки). Подвыражения удобны в тех случаях, когда требуется найти совпадение для всего шаблона, но получить совпадение только для его части. Скажем, если мы хотим найти телефонный номер и извлечь из него код города, мы заключаем часть шаблона, описывающую код города, в круглые скобки, превращая ее в подвыражение:

Подсчет совпадений

Еще одна типичная задача — подсчет количества совпадений регулярного выражения в строке. До выхода Oracle11g программисту приходилось в цикле перебирать и подсчитывать совпадения. Теперь для этого можно воспользоваться новой функцией REGEXP_COUNT . Общий синтаксис ее вызова:

Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска.

Замена текста REGEXP_REPLACE

Поиск и замена — одна из лучших областей применения регулярных выражений. Текст замены может включать ссылки на части исходного выражения (называемые обратными ссылками), открывающие чрезвычайно мощные возможности при работе с текстом. Допустим, имеется список имен, разделенный запятыми, и его содержимое необходимо вывести по два имени в строке. Одно из решений заключается в том, чтобы заменить каждую вторую запятую символом новой строки. Сделать это при помощи стандартной функции REPLACE нелегко, но с функцией REGEXP_REPLACE задача решается просто. Общий синтаксис ее вызова:

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

Результат выглядит так:

При вызове функции REGEXP_REPLACE передаются три аргумента:

  • names — исходная строка;
  • ‘([a-z A-Z]*),([a-z A-Z]*),’ — выражение, описывающее заменяемый текст (см. ниже);
  • ‘\1,\2 ‘ || chr(10) — текст замены. \1 и \2 — обратные ссылки, заложенные в основу нашего решения. Подробные объяснения также приводятся ниже.

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

  • ([a-z A-Z]*) Совпадение должно начинаться с имени.
  • , За именем должна следовать запятая.
  • ([a-z A-Z]*) Затем идет другое имя.
  • , И снова одна запятая.


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

Первое совпадение для нашего выражения, которое будет найдено при вызове REGEXP_REPLACE , выглядит так:

Два подвыражения соответствуют именам « Anna » и « Matt ». В основе нашего решения лежит возможность ссылаться на текст, совпавший с заданным подвыражением, через обратную ссылку. Обратные ссылки \1 и \2 в тексте замены ссылаются на текст, совпавший с первым и вторым подвыражением. Вот что происходит:

Вероятно, вы уже видите, какие мощные инструменты оказались в вашем распоряжении. Запятые из исходного текста попросту не используются. Мы берем текст, совпавший с двумя подвыражениями (имена «Anna» и «Matt»), и вставляем их в новую строку с одной запятой и одним символом новой строки.

Но и это еще не все! Текст замены легко изменить так, чтобы вместо запятой в нем использовался символ табуляции (ASCII-код 9):

Теперь результаты выводятся в два аккуратных столбца:

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

Максимализм и минимализм

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

Казалось бы, нужно искать серию символов, завершающуюся запятой:

Давайте посмотрим, что из этого получится:

Результат выглядит так:

Совсем не то. Что произошло? Дело в «жадности» регулярных выражений: для каждого элемента регулярного выражения подыскивается максимальное совпадение, состоящее из как можно большего количества символов. Когда мы с вами видим конструкцию:

у нас появляется естественное желание остановиться у первой запятой и вернуть строку « Anna ,». Однако база данных пытается найти самую длинную серию символов, завершающуюся запятой; база данных останавливается не на первой запятой, а на последней.

В версии Oracle Database 10g Release 1, в которой впервые была представлена поддержка регулярных выражений, возможности решения проблем максимализма были весьма ограничены. Иногда проблему удавалось решить изменением формулировки регулярного выражения — например, для выделения первого имени с завершающей запятой можно использовать выражение [^,]*, . Однако в других ситуациях приходилось менять весь подход к решению, часто вплоть до применения совершенно других функций.

Начиная с Oracle Database 10g Release 2, проблема максимализма отчасти упростилась с введением минимальных квантификаторов (по образцу тех, которые поддерживаются в Perl ). Добавляя вопросительный знак к квантификатору после точки, то есть превращая * в *?, я ищу самую короткую последовательность символов перед запятой:

Теперь результат выглядит так, как и ожидалось:

Минимальные квантификаторы останавливаются на первом подходящем совпадении, не пытаясь захватить как можно больше символов.

Подробнее о регулярных выражениях

Регулярные выражения на первый взгляд просты, но эта область на удивление глубока и нетривиальна. Они достаточно просты, чтобы вы начали пользоваться ими после прочтения этой статьи (хочется надеяться!), и все же вам предстоит очень много узнать. Некоторые источники информации от компании Oracle и издательства O’Reilly :

  • Oracle Database Application Developer’s Guide — Fundamentals. В главе 4 этого руководства описана поддержка регулярных выражений в Oracle.
  • Oracle Regular Expression Pocket Reference . Хороший вводный учебник по работе с регулярными выражениями; авторы — Джонатан Дженник ( Jonathan Gennick) и Питер Линсли (Peter Linsley). Питер является одним из разработчиков реализации регулярных выражений Oracle.
  • Mastering Oracle SQL. Одна из глав книги посвящена регулярным выражениям в контексте Oracle SQL. Отличный учебник для всех, кто хочет поднять свои навыки использования SQL на новый уровень.
  • Mastering Regular Expressions. Книга Джеффри Фридла ( Jeffrey Friedl) считается самым авторитетным источником информации об использовании регулярных выражений. Если вы хотите действительно глубоко изучить материал — читайте книгу Фридла.

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

REGEXP_LIKE ФУНКЦИЯ

В этом учебном пособии вы узнаете, как использовать Oracle условие REGEXP_LIKE (регулярные выражения) с синтаксисом и примерами.

Не следует путать с условием LIKE, который выполняет простое сопоставление с шаблоном.


Описание

Oracle условие REGEXP_LIKE позволяет выполнять регулярные выражения в предложении WHERE в запросах SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис для REGEXP_LIKE в Oracle/PLSQL:

Параметры или аргументы

expression
Символьное выражение такие как столбец или поле. Это могут быть следующие типы данных: VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB или NCLOB.

pattern
Шаблон. Регулярное выражение для сопоставления. Это может быть комбинацией следующих значений:

Значение Описание
^ Соответствует началу строки. При использовании match_parameter с ‘m’, соответствует началу строки в любом месте в пределах выражения.
$ Соответствует концу строки. При использовании match_parameter с ‘m’, соответствует концу строки в любом месте в пределах выражения.
* Соответствует нолю или более вхождений.
+ Соответствует одному или более вхождений.
? Соответствует нолю или одному вхождению.
. Соответствует любому символу, кроме NULL.
| Используется как «OR», чтобы указать более одной альтернативы.
[ ] Используется для указания списка совпадений, где вы пытаетесь соответствовать любому из символов в списке.
[^ ] Используется для указания списока nonmatching, где вы пытаетесь соответствовать любому символу, за исключением тех кто в списке.
( ) Используется для групповых выражений в качестве подвыражений.
Соответствует m раз.
Соответствие как минимум m раз.
Соответствие как минимум m раз, но не более n раз.
\n n представляет собой число от 1 до 9. Соответствует n-му подвыражению находящемуся в ( ) перед \n.
[..] Соответствует одному сопоставлению элемента, который может быть более одного символа.
[::] Соответствует классу символов.
[==] Соответствует классу эквивалентности
\d Соответствует цифровому символу.
\D Соответствует не цифровому символу.
\w Соответствует текстовому символу.
\W Соответствует не текстовому символу.
\s Соответствует символу пробел.
\S Соответствует не символу пробел.
\A Соответствует началу строки или соответствует концу строки перед символом новой строки.
\Z Соответствует концу строки.
*? Соответствует предыдущему шаблону ноль или более вхождений.
+? Соответствует предыдущему шаблону один или более вхождений.
?? Соответствует предыдущему шаблону ноль или одному вхождению.
? Соответствует предыдущему шаблону n раз.
? Соответствует предыдущему шаблону, по меньшей мере n раз.
? Соответствует предыдущему шаблону, по меньшей мере n раз, но не более m раз.

match_parameter
Необязательный. Это позволяет изменять поведение соответствия для условия REGEXP_LIKE. Это может быть комбинацией следующих значений:

Значение Описание
‘c’ Выполняет чувствительное к регистру согласование.
‘i’ Выполняет не чувствительное к регистру согласование.
‘n’ Позволяет период символа (.) для соответствия символа новой строки. По умолчанию, период метасимволы.
‘m’ Выражение допускает, что есть несколько строк, где ^ это начало строки, а $ это конец строки, независимо от позиции этих символов в выражении. По умолчанию предполагается, что выражение в одной строке.
‘x’ Игнорируются символы пробелов. По умолчанию, символы пробелов совпадают, как и любой другой символ.

Примечание

  • Условие REGEXP_LIKE использует входной набор символов для оценки строк.
  • Если вы укажите значение match_parameter как конфликт, то условие REGEXP_LIKE будет использовать последнее значение, чтобы разорвать конфликт.
  • Если match_parameter опущен, условие REGEXP_LIKE будет использовать чувствительность к регистру, как определено параметром NLS_SORT.

Смотрите также Oracle оператор LIKE.

Пример сопоставления с более чем одной альтернативой

Первый пример Oracle условия REGEXP_LIKE, который мы рассмотрим, предполагает использование | шаблона.

Объясним, как | шаблон работает в Oracle условии REGEXP_LIKE. Например:

How to do a regular expression replace in MySQL?

I have a table with

500k rows; varchar(255) UTF8 column filename contains a file name;

I’m trying to strip out various strange characters out of the filename — thought I’d use a character class: [^a-zA-Z0-9()_ .\-]

Now, is there a function in MySQL that lets you replace through a regular expression? I’m looking for a similar functionality to REPLACE() function — simplified example follows:

I know about REGEXP/RLIKE, but those only check if there is a match, not what the match is.

(I could do a » SELECT pkey_id,filename FROM foo WHERE filename RLIKE ‘[^a-zA-Z0-9()_ .\-]’ » from a PHP script, do a preg_replace and then » UPDATE foo . WHERE pkey_ >», but that looks like a last-resort slow & ugly hack)


12 Answers 12

With MySQL 8.0+ you could use natively REGEXP_REPLACE function.

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL , the return value is NULL .

Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators ( REGEXP , RLIKE ).

Regular expression support has been reimplemented using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. The REGEXP_LIKE() function performs regular expression matching in the manner of the REGEXP and RLIKE operators, which now are synonyms for that function. In addition, the REGEXP_INSTR() , REGEXP_REPLACE() , and REGEXP_SUBSTR() functions are available to find match positions and perform substring substitution and extraction, respectively.

But if you have access to your server, you could use a user defined function (UDF) like mysql-udf-regexp.

EDIT: MySQL 8.0+ you could use natively REGEXP_REPLACE . More in answer above.

Use MariaDB instead. It has a function

Note that you can use regexp grouping as well (I found that very useful):

My brute force method to get this to work was just:

  1. Dump the table — mysqldump -u user -p database table > dump.sql
  2. Find and replace a couple patterns — find /path/to/dump.sql -type f -exec sed -i ‘s/old_string/new_string/g’ <> \; , There are obviously other perl regeular expressions you could perform on the file as well.
  3. Import the table — mysqlimport -u user -p database table

If you want to make sure the string isn’t elsewhere in your dataset, run a few regular expressions to make sure they all occur in a similar environment. It’s also not that tough to create a backup before you run a replace, in case you accidentally destroy something that loses depth of information.

I recently wrote a MySQL function to replace strings using regular expressions. You could find my post at the following location:

Here is the function code:

we solve this problem without using regex this query replace only exact match string.

After executing query result:

I’m happy to report that since this question was asked, now there is a satisfactory answer! Take a look at this terrific package:

I found the package from this blog post as linked on this question.

UPDATE 2: A useful set of regex functions including REGEXP_REPLACE have now been provided in MySQL 8.0. This renders reading on unnecessary unless you’re constrained to using an earlier version.

The following expands upon the function provided by Rasika Godawatte but trawls through all necessary substrings rather than just testing single characters:

Demo

Limitations

  1. This method is of course going to take a while when the subject string is large. Update: Have now added minimum and maximum match length parameters for improved efficiency when these are known (zero = unknown/unlimited).
  2. It won’t allow substitution of backreferences (e.g. \1 , \2 etc.) to replace capturing groups. If this functionality is needed, please see this answer which attempts to provide a workaround by updating the function to allow a secondary find and replace within each found match (at the expense of increased complexity).
  3. If ^ and/or $ is used in the pattern, they must be at the very start and very end respectively — e.g. patterns such as (^start|end$) are not supported.
  4. There is a «greedy» flag to specify whether the overall matching should be greedy or non-greedy. Combining greedy and lazy matching within a single regular expression (e.g. a.*?b.* ) is not supported.

Usage Examples


The function has been used to answer the following StackOverflow questions:

MYSQL Regular Expressions (REGEXP) with Syntax & Examples

What are regular expressions?

Regular Expressions help search data matching complex criteria. We looked at wildcards in the previous tutorial. If you have worked with wildcards before, you may be asking why learn regular expressions when you can get similar results using the wildcards. Because, compared to wildcards, regular expressions allow us to search data matching even more complex criterion.

Basic syntax

The basic syntax for a regular expression is as follows

HERE —

  • «SELECT statements. « is the standard SELECT statement
  • «WHERE fieldname» is the name of the column on which the regular expression is to be performed on.
  • «REGEXP ‘pattern'» REGEXP is the regular expression operator and ‘pattern’ represents the pattern to be matched by REGEXP. RLIKE is the synonym for REGEXP and achieves the same results as REGEXP. To avoid confusing it with the LIKE operator, it better to use REGEXP instead.

Let’s now look at a practical example-

The above query searches for all the movie titles that have the word code in them. It does not matter whether the «code» is at the beginning, middle or end of the title. As long as it is contained in the title then it will be considered.

Let’s suppose that we want to search for movies that start with a, b, c or d , followed by any number of other characters, how would we go about to achieve that. We can use a regular expression together with the metacharacters to achieve our desired results.

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

Let’s now take a close look at our regular expression responsible for the above result.

‘^[abcd]’ the caret (^) means that the pattern match should be applied at the beginning and the charlist [abcd] means that only movie titles that start with a, b, c or d are returned in our result set.

Let’s modify our above script and use the NOT charlist and see what results we will get after executing our query.

Executing the above script in MySQL workbench against the myflixdb gives us the following results.

Let’s now take a close look at our regular expression responsible for the above results.

‘^[^abcd]’ the caret (^) means that the pattern match should be applied at the beginning and the charlist [^abcd] means that the movie titles starting with any of the enclosed characters is excluded from the result set.

Regular expression metacharacters

What we looked at in the above example is the simplest form of a regular expression. Let’s now look at more advanced regular expression pattern matches. Suppose we want to search for movie titles that start with the pattern «code» only using a regular expression, how would we go about it? The answer is metacharacters. They allow us to fine tune our pattern search results using regular expressions.

Char Description Example
* The asterisk (*) metacharacter is used to match zero (0) or more instances of the strings preceding it SELECT * FROM movies WHERE title REGEXP ‘da*’; will give all movies containing characters «da» .For Example, Da Vinci Code , Daddy’s Little Girls.
+ The plus (+) metacharacter is used to match one or more instances of strings preceding it. SELECT * FROM `movies` WHERE `title` REGEXP ‘mon+’; will give all movies containing characters «mon» .For Example, Angels and Demons.
? The question(?) metacharacter is used to match zero (0) or one instances of the strings preceding it. SELECT * FROM `categories` WHERE `category_name` REGEXP ‘com?’; will give all the categories containing string com .For Example, comedy , romantic comedy .
. The dot (.) metacharacter is used to match any single character in exception of a new line. SELECT * FROM movies WHERE `year_released` REGEXP ‘200.’; will give all the movies released in the years starting with characters «200» followed by any single character .For Example, 2005,2007,2008 etc.
[abc] The charlist [abc] is used to match any of the enclosed characters. SELECT * FROM `movies` WHERE `title` REGEXP ‘[vwxyz]’; will give all the movies containing any single character in «vwxyz» .For Example, X-Men, Da Vinci Code, etc.
[^abc] The charlist [^abc] is used to match any characters excluding the ones enclosed. SELECT * FROM `movies` WHERE `title` REGEXP ‘^[^vwxyz]’; will give all the movies containing characters other than the ones in «vwxyz».
[A-Z] The [A-Z] is used to match any upper case letter. SELECT * FROM `members` WHERE `postal_address` REGEXP ‘[A-Z]’; will give all the members that have postal address containing any character from A to Z. .For Example, Janet Jones with membership number 1.
[a-z] The [a-z] is used to match any lower case letter SELECT * FROM `members` WHERE `postal_address` REGEXP ‘[a-z]’; will give all the members that have postal addresses containing any character from a to z. .For Example, Janet Jones with membership number 1.
[0-9] The [0-9] is used to match any digit from 0 through to 9. SELECT * FROM `members` WHERE `contact_number` REGEXP ‘[0-9]’ will give all the members have submitted contact numbers containing characters «[0-9]» .For Example, Robert Phil.
^ The caret (^) is used to start the match at beginning. SELECT * FROM `movies` WHERE `title` REGEXP ‘^[cd]’; gives all the movies with the title starting with any of the characters in «cd» .For Example, Code Name Black, Daddy’s Little Girls and Da Vinci Code.
| The vertical bar (|) is used to isolate alternatives. SELECT * FROM `movies` WHERE `title` REGEXP ‘^[cd]|^[u]’; gives all the movies with the title starting with any of the characters in «cd» or «u» .For Example, Code Name Black, Daddy’s Little Girl, Da Vinci Code and Underworld — Awakening.
[[: :]] The [[:>:]] matches the end of words. SELECT * FROM `movies` WHERE `title` REGEXP ‘ack[[:>:]]’; gives all the movies with titles ending with the characters «ack» .For Example, Code Name Black.
[:class:] The [:class:] matches a character class i.e. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters. SELECT * FROM `movies` WHERE `title` REGEXP ‘[:alpha:]’; gives all the movies with titles contain letters only .For Example, Forgetting Sarah Marshal, X-Men etc. Movie like Pirates of the Caribbean 4 will be omitted by this query.

The backslash (\) is used to as an escape character. If we want to use it as part of the pattern in a regular expression, we should use double backslashes (\\)

MySQL | Регулярные выражения (Regexp)

MySQL поддерживает другой тип операции сопоставления с образцом на основе регулярных выражений и оператора REGEXP.

  1. Он обеспечивает мощное и гибкое сопоставление с шаблоном, которое может помочь нам реализовать мощные утилиты поиска для наших систем баз данных.
  2. REGEXP — это оператор, используемый при выполнении сопоставления с шаблоном регулярного выражения. RLIKE — это синоним.
  3. Он также поддерживает ряд метасимволов, которые обеспечивают большую гибкость и контроль при выполнении сопоставления с образцом.
  4. Обратная косая черта используется как escape-символ. Это учитывается только при сопоставлении с образцом, если используются двойные обратные слеши.
  5. Нечувствительный к регистру.
Шаблон Что соответствует шаблону
* Ноль или более экземпляров строки, предшествующей ему
+ Один или несколько экземпляров строк, предшествующих ему
, Любой отдельный персонаж
? Сопоставьте ноль или один экземпляр строк, предшествующих ему.
^ вставка (^) соответствует началу строки
$ Конец строки
[ABC] Любой символ, указанный в квадратных скобках
[^ А] Любой символ, не указанный в квадратных скобках
[AZ] соответствовать любой заглавной букве.
[AZ] соответствовать любой строчной букве
[0-9] совпадать с любой цифрой от 0 до 9.
[[: :]] соответствует концу слов.
[:учебный класс:] соответствует классу символов, т.е. [: alpha:] для сопоставления букв, [: space:] для сопоставления пробелов, [: punct:] соответствует пунктуации и [: upper:] для букв верхнего класса.
p1 | p2 | p3 Чередование; соответствует любому из паттернов p1, p2 или p3
n экземпляров предыдущего элемента
от m до n экземпляров предыдущего элемента

Примеры с объяснением:


    Совпадение начала строки (^):
    Дает все имена, начинающиеся с ‘sa’. Например, sam, samarth.

Сопоставьте конец строки ($):
Дает все имена, заканчивающиеся на ‘on’. Пример — нортон, мертон.

Соответствует нулю или одному экземпляру строк, предшествующих ему (?):
Дает все названия, содержащие «ком». Пример — комедия, романтическая комедия.

соответствует любому из паттернов p1, p2 или p3 (p1 | p2 | p3):
Дает все имена, содержащие «be» или «ae». Пример — Abel, Baer.

Соответствует любому символу, указанному в квадратных скобках ([abc]):
Дает все имена, содержащие «j» или «z». Пример — Lorentz, Rajs.

Соответствует любой строчной букве между ‘a’ и ‘z’- ([az]) ([az] и (.)):
Извлеките все имена, содержащие буквы в диапазоне «b» и «g», за которыми следует любой символ, за которым следует буква «a». Пример — Тобиас, sewall.

Соответствует любому отдельному символу (.)

Соответствует любому символу, не указанному в квадратных скобках. ([^ Abc]):
Дает все имена, не содержащие «j» или «z». Пример — нертон, sewall.

Соответствует концу слов [[:>:]]:
Дает все названия, заканчивающиеся символом «ack». Пример — черный.

Соответствует началу слов [[:

Соответствует классу персонажа [: class:]:
то есть [: нижний:] — строчные буквы, [: цифра:] — цифры и т. д.
Дает все заголовки, содержащие только буквенные символы. Пример — странные вещи, Мстители.

Регулярные выражения в MySQL

Регулярные выражения (они же regular expression или regex) представляют собой мощный путь для определения сложного поиска.

MySQL использует реализацию Henry Spencer’s, которая нацелена на соответствие POSIX 1003.2. MySQL использует ее расширенную версию.

Это упрощенное описание, которое опускает ряд деталей. За подробной информацией отсылаю Вас к man-странице Henry Spencer’s regex(7) , которая включена в дистрибутив исходного кода.

Регулярное выражение описывает набор строк. Самый простой regexp такой, который не имеет никаких специальных символов. Например, regexp hello соответствует hello и ничему другому.

Нетривиальные регулярные выражения используют некоторые специальные конструкции так, чтобы они могли соответствовать больше, чем одной строке. Например, regexp hello|word соответствует строке hello или word .

Как более сложный пример, regexp B[an]*s соответствует любой из строк Bananas , Baaaaas , Bs и любой другой строке, начинающейся с B , заканчивающейся на s и содержащей любое число символов в диапазоне от a до n между ними.


Приложение G. Регулярные выражения в MySQL

Регулярные выражения (regex, regexp) представляют собой мощный способ выполнения сложного поиска.

В MySQL используется расширенная версия предложенной Генри Спенсером (Henry Spencer) реализации регулярных выражений, которая ориентирована на соответствие POSIX 1003.2.

В данном разделе приведен упрощенный справочник; подробности здесь опущены. Чтобы получить более точную информацию, обращайтесь к странице руководства Генри Спенсера regex(7) , которая включена в дистрибутив исходного кода. See Приложение C, Благодарности.

Регулярное выражение описывает набор строк. Простейшее регулярное выражение не включает в себя специальных символов. Например, регулярное выражение hello означает совпадение с hello и ничего больше.

В нетривиальных регулярных выражениях используются определенные специальные конструкции — это обеспечивает возможность получать соответствие для более чем одной строки. Например, регулярное выражение hello|word соответствует как hello , так и word .

Можно привести и более сложный пример: регулярному выражению B[an]*s соответствует любая из строк: Bananas , Baaaaas , Bs , а также любая другая строка, начинающаяся с B , заканчивающаяся на s и содержащая любое количество символов a или n между ними.

В регулярном выражении могут использоваться любые специальные символы/структуры из числа приведенных ниже:

Соответствие началу строки.

Соответствие концу строки.

Соответствие любому символу (включая перевод строки).

Соответствие любой последовательности из нуля или более символов «a».

Соответствие любой последовательности из одного или более символов «a «.

Соответствие как нулю, так и одному символу «a».

Соответствие как последовательности de, так и последовательности abc.

Соответствие нулю или более вхождениям последовательности abc.

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

Можно записать как a<0,>.

Можно записать как a<1,>.

Можно записать как a<0,1>.

Точнее говоря, элемент, за которым следует ограничение, содержащее одно целое число i без запятой, соответствует последовательности, в точности состоящей из i вхождений данного элемента. Если за элементом следует ограничение, содержащее одно число i и запятую, то устанавливается соответствие для последовательности, содержащей i или более вхождений данного элемента. Если за элементом следует ограничение, содержащее два целых числа i и j , то устанавливается соответствие для последовательности от i до j (включительно) вхождений данного элемента. Оба аргумента должны находится в диапазоне от 0 до RE_DUP_MAX (по умолчанию 255) включительно. Если существуют оба аргумента, то второй должен быть больше первого или равен ему.

Устанавливает соответствие для любого символа, являющегося (или не являющегося, если используется ^ ) символом a , b , c , d или X . Для литерального включения символа ] следует сразу же после него написать открывающую скобку [ . Для литерального включения символа — он должен быть написан первым или последним. Таким образом, выражение [0-9] устанавливает соответствие для любой десятичной цифры. Любой символ, для которого не задано определенное значение внутри пары скобок [] , не имеет специального значения и совпадает только с самим собой.

Последовательность символов данного элемента сравнения. Эта последовательность представляет собой единственный элемент из списка в выражении в скобках. Выражение в скобках, содержащее многосимвольный сравнивающий элемент, может, следовательно, искать соответствие более, чем одного, например, если последовательность сравнения включает в себя элемент сравнения ch , то регулярное выражение [[.ch.]]*c устанавливает соответствие с первыми пятью символами выражения chchcc .

Класс эквивалентности, означающий, что последовательности символов всех элементов сравнения, включенных в данный класс, эквивалентны между собой. Например, если o и (+) являются членами класса эквивалентности, то последовательности [[=o=]] , [[=(+)=]] и [o(+)] все являются синонимичными. Класс эквивалентности может не быть конечной точкой диапазона.

Имя класса символов, заключенное внутри выражения в скобках [: имя :] , обозначает список всех символов, принадлежащих данному классу. Имена стандартных классов символов следующие:

Имя Имя Имя
alnum digit punct
alpha graph space
blank lower upper
cntrl print xdigit

Они обозначают классы символов, определенные на странице ctype(3) . Локаль может предоставлять другие классы. Класс символов не может использоваться как конечная точка диапазона.

Эти выражения устанавливают соответствие с нулевой строкой в начале и в конце слова соответственно. Слово определяется как последовательность символов слова, которой не предшествуют и за которой не следуют символы слова. Под символом слова понимается любая буква или цифра (как определено в ctype(3) ) или подчеркивание ( _ ).

Назад Вперед
Пред. Глава След. Глава
Приложение F. Переменные окружения Начало Книги Приложение H. GNU General Public License

Если Вы не нашли что искали, то рекомендую воспользоваться поиском по сайту:

Илон Маск рекомендует:  Что такое код ischarlower
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL