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

Функции для работы с текстовыми строками с помощью регулярных выражений. В регулярных выражениях используется синтаксис 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 не найдено, возвращает пустой список.

Параметры

  • <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_false,
    regexp_full_match('Tengri', '.')        AS result_false,
    regexp_full_match('Tengri', '.*')       AS result_true,
    regexp_full_match('Tengri', '^\w+gri$') AS result_true;
+--------------+--------------+-------------+-------------+
| result_false | result_false | result_true | result_true |
+--------------+--------------+-------------+-------------+
| false        | false        | true        | true        |
+--------------+--------------+-------------+-------------+

regexp_matches()

Описание

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

regexp_matches(string, regexp)

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

Посмотреть примеры 
SELECT
    regexp_matches('Tengri', '.+T') AS result_false,
    regexp_matches('Tengri', '.*T') AS result_true;
+--------------+-------------+
| result_false | result_true |
+--------------+-------------+
| 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                  |          |
+-----------------------+----------+