Функции для регулярных выражений

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

regexp_extract()

Описание

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

regexp_extract(string, regexp)

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

Посмотреть примеры 
SELECT
    regexp_extract('Tengri', '..') AS result_1,
    regexp_extract('Tengri', 'n.*') AS result_2,
    regexp_extract('Tengri', '.*') AS result_3,
    regexp_extract('Tengri', '^.{5}') AS result_4,
    regexp_extract('Tengri', '[TNGRi]$') AS result_5,
    regexp_extract('Tengri', '.{7}') AS result_empty;
+----------+----------+----------+----------+----------+--------------+
| result_1 | result_2 | result_3 | result_4 | result_5 | result_empty |
+----------+----------+----------+----------+----------+--------------+
| Te       | ngri     | Tengri   | Tengr    | i        |              |
+----------+----------+----------+----------+----------+--------------+

regexp_extract_all()

Описание

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

regexp_extract_all(string, regexp[, <num>])

Если подстрок в string, накрытых шаблоном regexp не найдено, возвращает пустой список.

Параметры

  • <num> — номер группы внутри шаблона, которую следует вернуть (для каждой подстроки). По умолчанию (если параметр не задан) возвращается вся подстрока. Нумерация групп начинается с 1. Группы в шаблоне выделяются круглыми скобками.

    Подробнее о параметре на примерах

    Извлечем из текста все сочетания букв с точкой после них:

    SELECT
    regexp_extract_all('My name is Tengri. My nickname is TNGRi.',
                       '(\w+)(\.)')
AS result;
    +------------------+
|      result      |
+------------------+
| {Tengri.,TNGRi.} |
+------------------+

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

    SELECT
    regexp_extract_all('My name is Tengri. My nickname is TNGRi.',
                       '(\w+)(\.)',
                       1)
AS result;
    +----------------+
|     result     |
+----------------+
| {Tengri,TNGRi} |
+----------------+
Посмотреть еще примеры 
SELECT
    regexp_extract_all('My name is Tengri. My nickname is TNGRi.', '\w+')
AS words;
+------------------------------------------+
|                   words                  |
+------------------------------------------+
| {My,name,is,Tengri,My,nickname,is,TNGRi} |
+------------------------------------------+

Используем комбинацию функций regexp_extract_all и unnest, чтобы извлечь данные из структурированного текста:

CREATE TABLE text_table(text_data VARCHAR);

INSERT INTO text_table VALUES
('Name: "Tengri", Nickname:"TNGRi"'),
('Country: "Russia", Capital:"Moscow"');

SELECT
    unnest(
        regexp_extract_all(text_data, '(\w+):\s*"(.*?)"', 1)
          ) AS key,
    unnest(
        regexp_extract_all(text_data, '(\w+):\s*"(.*?)"', 2)
          ) AS value
FROM text_table;
+----------+--------+
|    key   | value  |
+----------+--------+
| Name     | Tengri |
+----------+--------+
| Nickname | TNGRi  |
+----------+--------+
| Country  | Russia |
+----------+--------+
| Capital  | Moscow |
+----------+--------+

regexp_full_match()

Описание

Проверяет, накрывает ли регулярное выражение строку полностью.
Использование

regexp_full_match(string, regexp)

Если шаблон регулярного выражения regexp полностью накрывает строку string, то возвращает true, иначе — false.

Посмотреть примеры 
SELECT
    regexp_full_match('Tengri', 'gri$')     AS result_1, -- false expected
    regexp_full_match('Tengri', '.')        AS result_2, -- false expected
    regexp_full_match('Tengri', '.*')       AS result_3, -- true expected
    regexp_full_match('Tengri', '^\w+gri$') AS result_4; -- true expected
+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| false    | false    | true     | true     |
+----------+----------+----------+----------+

regexp_matches()

Описание

Проверяет, содержится ли регулярное выражение внутри строки.
Использование

regexp_matches(string, regexp)

Если внутри строки string найдена хотя бы одна подстрока, накрытая шаблоном regexp, то возвращает true, иначе — false.

Посмотреть примеры 
SELECT
    regexp_matches('Tengri', '.+T') AS result_1, -- false expected
    regexp_matches('Tengri', '.*T') AS result_2; -- true expected
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| false    | true     |
+----------+----------+

regexp_replace()

Описание

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

regexp_replace(string, regexp, target)

Если внутри строки string найдена подстрока, накрытая шаблоном regexp, то она заменяется на строку target. Если таких подстрок найдено несколько, то заменяется только первая. Если подстрок не найдено, возвращается исходная строка string.

Посмотреть примеры 
SELECT
    regexp_replace('Tengri', '.', 't') AS result_1,
    regexp_replace('Tengri', '.*', 't') AS result_2,
    regexp_replace('Tengri', 'e.*r', 'NGR') AS result_3,
    regexp_replace('Tengri', 'a', 't') AS result_4;
+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| tengri   | t        | TNGRi    | Tengri   |
+----------+----------+----------+----------+

regexp_split_to_array()

Описание

Разрезает строку на части, разделенные регулярным выражением, и возвращает части в виде массива.
Использование

regexp_split_to_array(string, regexp)
Псевдонимы

string_split_regex()

Если в строке string найдены подстроки, накрытые шаблоном regexp, то возвращаются части исходной строки, не накрытые шаблоном, в виде массива. Если найденные подстроки стоят в начале или в конце исходной строки, то в результирующий массив попадут пустые строки для начала и конца строки. Если подстрок не найдено, то будет возвращен массив из одной исходной строки.

Посмотреть примеры 
SELECT
    string_split_regex('My name is Tengri. My nickname is TNGRi.', '\.\s')
AS sentences,
    string_split_regex('My name is Tengri. My nickname is TNGRi.', '[\.\s]+')
AS words;
+-------------------------------------------+-------------------------------------------+
|                 sentences                 |                   words                   |
+-------------------------------------------+-------------------------------------------+
| {My name is Tengri,My nickname is TNGRi.} | {My,name,is,Tengri,My,nickname,is,TNGRi,} |
+-------------------------------------------+-------------------------------------------+

regexp_split_to_table()

Описание

Разрезает строку на части, разделенные регулярным выражением, и возвращает части в виде строк.
Использование

regexp_split_to_table(string, regexp)

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

Посмотреть примеры 
SELECT
    regexp_split_to_table('My name is Tengri. My nickname is TNGRi.', '\.\s')
AS sentences,
    regexp_split_to_table('My name is Tengri. My nickname is TNGRi.', '[\.\s]+')
AS words;
+-----------------------+----------+
|       sentences       |   words  |
+-----------------------+----------+
| My name is Tengri     | My       |
+-----------------------+----------+
| My nickname is TNGRi. | name     |
+-----------------------+----------+
| null                  | is       |
+-----------------------+----------+
| null                  | Tengri   |
+-----------------------+----------+
| null                  | My       |
+-----------------------+----------+
| null                  | nickname |
+-----------------------+----------+
| null                  | is       |
+-----------------------+----------+
| null                  | TNGRi    |
+-----------------------+----------+
| null                  |          |
+-----------------------+----------+

Оператор ~

Описание

Проверяет, накрывает ли регулярное выражение строку полностью.
Использование

string ~ regexp

Может использоваться с оператором отрицания !: string !~ regexp.

Полностью синонимичен оператору SIMILAR TO.

Посмотреть примеры 
SELECT
    'Tengri' !~ 'TNGRi' AS result_1,
    'Tengri' ~ 'T.*'    AS result_2,
    'Tengri' ~ 'T.+i'   AS result_3,
    'TNGRi'  ~ 'T.+i'   AS result_4,
    'T.+i'   ~ 'T.+i'   AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | true     | true     | true     | true     |
+----------+----------+----------+----------+----------+