FirebirdSQL logo
 Типы данныхОператоры определения данных (DDL) 
Синтаксис регулярных выражений SQL

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

<regular expression> ::= <regular term> ['|' <regular term> ...]

<regular term> ::= <regular factor> ...

<regular factor> ::= <regular primary> [<quantifier>]

<quantifier> ::= ? | * | + | '{' <m> [,[<n>]] '}'

<m>, <n> ::= целые положительные числа, если присутвуют оба числа, то <m> <= <n>

<regular primary> ::=
    <character> | <character class> | %
  | (<regular expression>)

<character> ::= <escaped character> | <non-escaped character>

<escaped character> ::=
  <escape-char> <special character> | <escape-char> <escape-char>

<special character> ::= любой из символов []()|^-+*%_?{}

<non-escaped character> ::=
  любой символ за исключением <special character>
  и не эквивалентный <escape-char> (если задан)

<character class> ::=
    '_' | '[' <member> ... ']' | '[^' <non-member> ... ']'
  | '[' <member> ... '^' <non-member> ... ']'

<member>, <non-member> ::= <character> | <range> | <predefined class>

<range> ::= <character>-<character>

<predefined class> ::= '[:' <predefined class name> ':]'

<predefined class name> ::=
  ALPHA | UPPER | LOWER | DIGIT | ALNUM | SPACE | WHITESPACE
Создание регулярных выражений

В этом разделе представлены элементы и правила построения регулярных выражений SQL.

docnext count = 18

Символы

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

[ ] ( ) | ^ - + * % _ ? { }

... и управляющих символов (escaped character), если они заданы.

Регулярному выражению, не содержащему специальных или управляющих символов, соответствует только полностью идентичные строки (в зависимости от используемой сортировки). То есть это функционирует точно так же, как оператор “=”:

'Apple' SIMILAR TO 'Apple' -- TRUE
'Apples' SIMILAR TO 'Apple' -- FALSE
'Apple' SIMILAR TO 'Apples' -- FALSE
'APPLE' SIMILAR TO 'Apple' -- в зависимости от сортировки
Шаблоны

Известным SQL шаблонам ‘_’ и ‘%’ соответствует любой единственный символ и строка любой длины, соответственно:

'Birne' SIMILAR TO 'B_rne' -- TRUE
'Birne' SIMILAR TO 'B_ne' -- FALSE
'Birne' SIMILAR TO 'B%ne' -- TRUE
'Birne' SIMILAR TO 'Bir%ne%' -- TRUE
'Birne' SIMILAR TO 'Birr%ne' -- FALSE

Обратите внимание, что шаблон ‘%’ также соответствует пустой строке.

Классы символов

Набор символов, заключённый в квадратные скобки определяют класс символов.Символ в строке соответствует классу в шаблоне, если символ является элементом класса:

'Citroen' SIMILAR TO 'Cit[arju]oen' -- TRUE
'Citroen' SIMILAR TO 'Ci[tr]oen' -- FALSE
'Citroen' SIMILAR TO 'Ci[tr][tr]oen' -- TRUE

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

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

'Datte' SIMILAR TO 'Dat[q-u]e' -- TRUE
'Datte' SIMILAR TO 'Dat[abq-uy]e' -- TRUE
'Datte' SIMILAR TO 'Dat[bcg-km-pwz]e' -- FALSE
Предопределённые классы символов

Следующие предопределенные классы символов также могут использоваться в определении класса:

[:ALPHA:]

Латинские буквы a…​z и A…​Z.Этот класс также включает символы с диакритическими знаками при нечувствительных к акцентам сортировках.

[:DIGIT:]

Десятичные цифры 0…​9.

[:ALNUM:]

Объединение [:ALPHA:] и [:DIGIT:].

[:UPPER:]

Прописные (в верхнем регистре) латинские буквы A…​Z.Также включает в себя символы в нижнем регистре при нечувствительных к регистру сортировках и символы с диакритическими знаками при нечувствительных к акцентам сортировках.

[:LOWER:]

Строчные (в нижнем регистре) латинские буквы a…​z.Также включает в себя символы в верхнем регистре при нечувствительных к регистру сортировках и символы с диакритическими знаками при нечувствительных к акцентам сортировках.

[:SPACE:]

Символ пробела (ASCII 32).

[:WHITESPACE:]

Горизонтальная табуляция (ASCII 9), перевод строки (ASCII 10), вертикальная табуляция (ASCII 11), разрыв страницы (ASCII 12), возврат каретки (ASCII 13) и пробел (ASCII 32).

Включение в оператор SIMILAR TO предопределённого класса имеет тот же эффект, как и включение всех его элементов.Использование предопределённых классов допускается только в пределах определения класса.Если вам нужно сопоставление только с предопределённым классом и ничего больше, то поместите дополнительную пару скобок вокруг него.

'Erdbeere' SIMILAR TO 'Erd[[:ALNUM:]]eere' -- TRUE
'Erdbeere' SIMILAR TO 'Erd[[:DIGIT:]]eere' -- FALSE
'Erdbeere' SIMILAR TO 'Erd[a[:SPACE:]b]eere' -- TRUE
'Erdbeere' SIMILAR TO '[[:ALPHA:]]' -- FALSE
'E' SIMILAR TO '[[:ALPHA:]]' -- TRUE

Если определение класса запускается со знаком вставки (^), то все, что следует за ним, исключается из класса.Все остальные символы проверяются.

'Framboise' SIMILAR TO 'Fra[^ck-p]boise' -- FALSE
'Framboise' SIMILAR TO 'Fr[^a][^a]boise' -- FALSE
'Framboise' SIMILAR TO 'Fra[^[:DIGIT:]]boise' -- TRUE

Если знак вставки (^) находится не в начале последовательности, то класс включает в себя все символы до него и исключает символы после него.

'Grapefruit' SIMILAR TO 'Grap[a-m^f-i]fruit' -- TRUE
'Grapefruit' SIMILAR TO 'Grap[abc^xyz]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abc^de]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abe^de]fruit' -- FALSE
'3' SIMILAR TO '[[:DIGIT:]^4-8]' -- TRUE
'6' SIMILAR TO '[[:DIGIT:]^4-8]' -- FALSE

Наконец, уже упомянутый подстановочный знак ‘_’ является собственным классом символов, соответствуя любому единственному символу.

Кванторы

Вопросительный знак (‘?’) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 0 или 1 раз:

'Hallon' SIMILAR TO 'Hal?on' -- FALSE
'Hallon' SIMILAR TO 'Hal?lon' -- TRUE
'Hallon' SIMILAR TO 'Halll?on' -- TRUE
'Hallon' SIMILAR TO 'Hallll?on' -- FALSE
'Hallon' SIMILAR TO 'Halx?lon' -- TRUE
'Hallon' SIMILAR TO 'H[a-c]?llon[x-z]?' -- TRUE

Звёздочка (‘*’) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 0 или более раз:

'Icaque' SIMILAR TO 'Ica*que' -- TRUE
'Icaque' SIMILAR TO 'Icar*que' -- TRUE
'Icaque' SIMILAR TO 'I[a-c]*que' -- TRUE
'Icaque' SIMILAR TO '_*' -- TRUE
'Icaque' SIMILAR TO '[[:ALPHA:]]*' -- TRUE
'Icaque' SIMILAR TO 'Ica[xyz]*e' -- FALSE

Знак плюс (‘+’) сразу после символа или класса указывает на то, что для соответствия предыдущий элемент должен встретиться 1 или более раз:

'Jujube' SIMILAR TO 'Ju_+' -- TRUE
'Jujube' SIMILAR TO 'Ju+jube' -- TRUE
'Jujube' SIMILAR TO 'Jujuber+' -- FALSE
'Jujube' SIMILAR TO 'J[jux]+be' -- TRUE
'Jujube' SIMILAR TO 'J[[:DIGIT:]]+ujube' -- FALSE

Если символ или класс сопровождаются числом, заключённым в фигурные скобки (‘{’ и ‘}’), то для соответствия необходимо повторение элемента точно это число раз:

'Kiwi' SIMILAR TO 'Ki{2}wi' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{2}i' -- TRUE
'Kiwi' SIMILAR TO 'K[ipw]{2}' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{3}' -- TRUE

Если число сопровождается запятой (‘,’), то для соответствия необходимо повторение элемента как минимум это число раз:

'Limone' SIMILAR TO 'Li{2,}mone' -- FALSE
'Limone' SIMILAR TO 'Li{1,}mone' -- TRUE
'Limone' SIMILAR TOto 'Li[nezom]{2,}' -- TRUE

Если фигурные скобки содержат два числа (m и n), разделённые запятой, и второе число больше первого, то для соответствия элемент должен быть повторен, как минимум, m раз и не больше n раз:

'Mandarijn' SIMILAR TO 'M[a-p]{2,5}rijn' -- TRUE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}rijn' -- FALSE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}arijn' -- TRUE

Кванторы ‘?’, ‘*’ и ‘+’ являются сокращением для {0,1}, {0,} и {1,}, соответственно.

Термин ИЛИ

В условиях регулярных выражений можно использовать оператор ИЛИ ‘|’. Соответствие произошло, если строка параметра соответствует, по крайней мере, одному из условий:

'Nektarin' SIMILAR TO 'Nek|tarin' -- FALSE
'Nektarin' SIMILAR TO 'Nektarin|Persika' -- TRUE
'Nektarin' SIMILAR TO 'M_+|N_+|P_+' -- TRUE
Подвыражения

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

'Orange' SIMILAR TO 'O(ra|ri|ro)nge' -- TRUE
'Orange' SIMILAR TO 'O(r[a-e])+nge' -- TRUE
'Orange' SIMILAR TO 'O(ra){2,4}nge' -- FALSE
'Orange' SIMILAR TO 'O(r(an|in)g|rong)?e' -- TRUE
Экранирование специальных символов

Для исключения из процесса сопоставления специальных символов (которые часто встречаются в регулярных выражениях) их надо экранировать.Специальных символов экранирования по умолчанию нет — их при необходимости определяет пользователь:

'Peer (Poire)' SIMILAR TO 'P[^ ]+ \(P[^ ]+\)' ESCAPE '\' -- TRUE
'Pera [Pear]' SIMILAR TO 'P[^ ]+ #[P[^ ]+#]' ESCAPE '#' -- TRUE
'Paron-Appledryck' SIMILAR TO 'P%$-A%' ESCAPE '$' -- TRUE
'Parondryck' SIMILAR TO 'P%--A%' ESCAPE '-' -- FALSE

IS DISTINCT FROM

Доступно в

DSQL, PSQL.

Синтаксис
<operand1> IS [NOT] DISTINCT FROM <operand2>

Два операнда считают DISTINCT (различными), если они имеют различные значения, или если одно из них — NULL, а другое нет.Они считаются NOT DISTINCT (равными), если имеют одинаковые значения или оба имеют значение NULL.

IS [NOT] DISTINCT FROM всегда возвращает TRUE или FALSE и никогда UNKNOWN (NULL) (неизвестное значение). Операторы ‘=’ и ‘<>’, наоборот, вернут UNKNOWN (NULL), если один или оба операнда имеют значение NULL.

Table 1. Результаты выполнения различных операторов сравнения

Характеристики операнда

Результаты различных операторов

=

IS NOT DISTINCT FROM

<>

IS DISTINCT FROM

Одинаковые значения

TRUE

TRUE

FALSE

FALSE

Различные значения

FALSE

FALSE

TRUE

TRUE

Оба NULL

UNKNOWN

TRUE

UNKNOWN

FALSE

Одно NULL

UNKNOWN

FALSE

UNKNOWN

TRUE
Example 1. Использование предиката IS [NOT] DISTINCT FROM
SELECT ID, NAME, TEACHER
FROM COURSES
WHERE START_DAY IS NOT DISTINCT FROM END_DAY

IF (NEW.JOB IS DISTINCT FROM OLD.JOB) THEN
  POST_EVENT 'JOB_CHANGED';
См. также:

[fblangref-commons-isnotboolean], [fblangref-commons-isnotnull].

Логический IS [NOT]

Доступно в

DSQL, PSQL.

Синтаксис
<value> IS [NOT] {TRUE | FALSE | UNKNOWN}

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

Для логического типа данных предикат IS [NOT] UNKNOWN эквивалентен IS [NOT] NULL.

Note
Замечание:

В правой части предиката могут быть использованы только литералы TRUE, FALSE, UNKNOWN, но не выражения.
Example 1. Использование оператора IS с логическим типом данных
-- Проверка FALSE значения
SELECT * FROM TBOOL WHERE BVAL IS FALSE
ID           BVAL
============ =======
2            <false>
-- Проверка UNKNOWN значения
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN
ID           BVAL
============ =======
3            <null>

IS [NOT] NULL

Доступно в

DSQL, PSQL.

Синтаксис
<value> IS [NOT] NULL

Поскольку NULL не является значением, эти операторы не являются операторами сравнения.Оператор IS [NOT] NULL проверяет, что выражение слева имеет значение (IS NOT NULL) или не имеет значения (IS NULL)

Example 1. Использование предиката IS [NOT] NULL

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

SELECT *
FROM SALES
WHERE SHIP_DATE IS NULL;

Предикаты существования

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

EXISTS

Доступно в

DSQL, PSQL, ESQL.

Синтаксис
[NOT] EXISTS (<select_stmt>)

Предикат EXISTS использует подзапрос в качестве аргумента.Если результат подзапроса будет содержать хотя бы одну запись, то предикат оценивается как истинный (TRUE), в противном случае предикат оценивается как ложный (FALSE).

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

Предикат NOT EXISTS возвращает FALSE, если результат подзапроса будет содержать хотя бы одну запись, в противном случае предикат вернёт TRUE.

Example 1. Предикат EXISTS

Найти тех сотрудников, у которых есть проекты.

SELECT *
FROM employee
WHERE EXISTS (SELECT *
              FROM
                employee_project ep
              WHERE
                ep.emp_no = employee.emp_no)
Example 2. Предикат NOT EXISTS

Найти тех сотрудников, у которых нет проектов.

SELECT *
FROM employee
WHERE NOT EXISTS (SELECT *
                  FROM
                    employee_project ep
                  WHERE
                    ep.emp_no = employee.emp_no)

IN

Доступно в

DSQL, PSQL, ESQL.

Синтаксис
<value> [NOT] IN (<select_stmt> | <value_list>)

<value_list> ::= <value_1> [, <value_2> ...]

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

(<value> = <value_1> [OR <value> = <value_2> ...])

При использовании предиката IN в поисковых условиях DML запросов, оптимизатор Firebird может использовать индекс по искомому столбцу, если он определён.

Во второй форме предикат IN проверяет, присутствует (или отсутствует, при использовании NOT IN) ли значение выражения слева в результате выполнения подзапроса справа.Результат подзапроса может содержать только один столбец, иначе будет выдана ошибка“count of column list and variable list do not match”.

Запросы с использованием предиката IN с подзапросом, можно переписать на аналогичный запрос с использованием предиката EXISTS.Например, следующий запрос:

SELECT
  model, speed, hd
FROM PC
WHERE
  model IN (SELECT model
            FROM product
            WHERE maker = 'A');

Можно переписать на аналогичный запрос с использованием предиката EXISTS:

SELECT
  model, speed, hd
FROM PC
WHERE
  EXISTS (SELECT *
          FROM product
          WHERE maker = 'A'
            AND product.model = PC.model);

Однако, запрос с использованием NOT IN не всегда даст тот же результат, что запрос NOT EXISTS.Причина заключается в том, что предикат EXISTS всегда возвращает TRUE или FALSE, тогда как предикат IN может вернуть NULL в следующих случаях:

  1. Когда проверяемое значение равно NULL и список в IN не пуст.

  2. Когда проверяемое значение не имеет совпадений в списке IN и одно из значений является NULL.

В этих двух случаях предикат IN вернёт NULL, в то время как соответствующий предикат EXISTS вернёт FALSE.В поисковых условиях или операторе IF оба результата обозначают “провал” и обрабатываются одинаково.

Однако на тех же данных NOT IN вернёт NULL, в то время как EXISTS вернёт TRUE, что приведёт к противоположному результату.

Это можно продемонстрировать следующим примером.

Предположим у вас есть такой запрос:

-- Ищем людей, которые не родились в тот же день, что
-- известные жители Нью-Йорка
SELECT P1.name AS NAME
FROM Personnel P1
WHERE P1.birthday NOT IN (SELECT C1.birthday
                          FROM Celebrities C1
                          WHERE С1.birthcity = 'New York');

Можно предположить, что аналогичный результат даст запрос с использованием предиката NOT EXISTS:

-- Ищем людей, которые не родились в тот же день, что
-- известные жители Нью-Йорка
SELECT P1.name AS NAME
FROM Personnel P1
WHERE NOT EXISTS (SELECT *
                  FROM Celebrities C1
                  WHERE C1.birthcity = 'New York'
                    AND C1.birthday = P1.birthday);

Допустим, что в Нью-Йорке всего один известный житель, и его дата рождения неизвестна.При использовании предиката EXISTS подзапрос внутри него не выдаст результатов, так как при сравнении дат рождения с NULL результатом будет UNKNOWN.Это приведёт к тому, что результат предиката NOT EXISTS будет истинен для каждой строки основного запроса.В то время как результатом предиката NOT IN будет UNKNOWN и ни одна строка не будет выведена.

Example 1. Предикат IN

Найти сотрудников с именами “Pete”, “Ann” и “Roger”:

SELECT *
FROM EMPLOYEE
WHERE FIRST_NAME IN ('Pete', 'Ann', 'Roger');
Example 2. Поисковый предикат IN

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

SELECT
  model, speed, hd
FROM PC
WHERE
  model IN (SELECT model
            FROM product
            WHERE maker STARTING WITH 'A');
См. также:

EXISTS.

SINGULAR

Доступно в

DSQL, PSQL, ESQL.

Синтаксис
[NOT] SINGULAR (<select_stmt>)

Предикат SINGULAR использует подзапрос в качестве аргумента и оценивает его как истинный, если подзапрос возвращает одну и только одну строку результата, в противном случае предикат оценивается как ложный.Результат подзапроса может содержать несколько столбцов, поскольку значения не проверяются.Данный предикат может принимать только два значения: истина (TRUE) и ложь (FALSE).

Example 1. Предикат SINGULAR

Найти тех сотрудников, у которых есть только один проект.

SELECT *
FROM employee
WHERE SINGULAR (SELECT *
                FROM
                  employee_project ep
                WHERE
                  ep.emp_no = employee.emp_no)

Количественные предикаты подзапросов

Квантором называется логический оператор, задающий количество объектов, для которых данное утверждение истинно.Это логическое количество, а не числовое; оно связывает утверждение с полным множеством возможных объектов.Такие предикаты основаны на формальных логических квантификаторах общности и существования, которые распознаются формальной логикой.

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

<value expression> <comparison operator> <quantifier> <subquery>

ALL

Доступно в

DSQL, PSQL.

Синтаксис
<value> <op> ALL (<select_stmt>)

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

Example 1. Квантор ALL

Вывести только тех заказчиков, чьи оценки выше, чем у каждого заказчика в Париже.

SELECT *
FROM Customers
WHERE rating > ALL
      (SELECT rating
       FROM Customers
       WHERE city = 'Paris')
Important

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

Тем не менее это нормально согласуется с формальной логикой: если множество пусто, то предикат верен 0 раз, т.е.для каждой строки в множестве.

ANY и SOME

Доступно в

DSQL, PSQL.

Синтаксис
<value> <op> {ANY | SOME} (<select_stmt>)

Эти два квантора идентичны по поведению.Очевидно, оба представлены в стандарте SQL для взаимозаменяемого использования с целью улучшения читаемости операторов.При использовании квантора ANY или SOME, предикат является истинным, если любое из значений выбранное подзапросом удовлетворяет условию в предикате внешнего запроса.Если подзапрос не возвращает ни одной строки, то предикат автоматически считается ложным.

Example 1. Квантор ANY

Вывести только тех заказчиков, чьи оценки выше, чем у какого-либо заказчика в Риме.

SELECT *
FROM Customers
WHERE rating > ANY
      (SELECT rating
       FROM Customers
       WHERE city = 'Rome')