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

В этой статье мы узнаем о PostgreSQL Regex. PostgreSQL использует регулярные выражения POSIX или «Интерфейс портативной операционной системы для Unix», которые лучше, чем операторы LIKE и SIMILAR TO, используемые для сопоставления с образцом (pattern matching).

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

Regex означает регулярные выражения. Regex — это последовательность символов, определяющая шаблон, позволяющий фильтровать данные в PostgreSQL . Оператор TILDE (~) и подстановочный оператор «.*» используются для реализации регулярных выражений PostgreSQL.

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

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

Функциональность операторов LIKE и SIMILAR TO по сути одинакова. Давайте теперь обсудим эти операторы и рассмотрим, как применять их к регулярным выражениям.

Операторы сопоставления регулярных выражений PostgreSQL

Как работает Regex PostgreSQL?

Пример таблицы и варианты использования

Мы рассмотрим следующие варианты использования, чтобы понять работу PostgreSQL Regex.

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

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

СОЗДАТЬ ТАБЛИЦУ

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);

ВСТАВЬТЕ ЗНАЧЕНИЯ В ТАБЛИЦУ

INSERT INTO Email(Name,Email) VALUES('Pratibha','pratibha67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Bhavya','bhavyaa@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Disha','disha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Divanshi','divanshi23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Srishti','srishti@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Kartik','kartik@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Rytham','rytham6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Madhav','madhav@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Tanisha','tanisha@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Radhika','radhika41@spguide.com');

ВЫВОД

SELECT *
FROM Email;

—--OUTPUT—--

id |   name   |          email          
----+----------+-------------------------
  1 | Pratibha | pratibha67@sqlguide.edu
  2 | Bhavya   | bhavyaa@pythonguide.com
  3 | Disha    | disha@sqlguide.edu
  4 | Divanshi | divanshi23@spguide.com
  5 | Srishti  | srishti@sqlguide.edu
  6 | Kartik   | kartik@pythonguide.com
  7 | Rytham   | rytham6@sqlguide.edu
  8 | Madhav   | madhav@tsinfo.edu
  9 | Tanisha  | tanisha@pythonguide.com
 10 | Radhika  | radhika41@spguide.com
(10 rows)

Давайте разберемся с синтаксисом и основными принципами работы PostgreSQL Regex на следующем примере:

SELECT *
FROM Email
WHERE Email ~ '^.*$';

—--OUTPUT—--

id |   name   |          email          
----+----------+-------------------------
  1 | Pratibha | pratibha67@sqlguide.edu
  2 | Bhavya   | bhavyaa@pythonguide.com
  3 | Disha    | disha@sqlguide.edu
  4 | Divanshi | divanshi23@spguide.com
  5 | Srishti  | srishti@sqlguide.edu
  6 | Kartik   | kartik@pythonguide.com
  7 | Rytham   | rytham6@sqlguide.edu
  8 | Madhav   | madhav@tsinfo.edu
  9 | Tanisha  | tanisha@pythonguide.com
 10 | Radhika  | radhika41@spguide.com
(10 rows)

Как показано выше, мы использовали PostgreSQL Regex , используя оператор TILDE ( ~ ) и подстановочный знак ' .* '. Этот запрос выберет все записи из таблицы «Электронная почта» с действительным адресом электронной почты.

Поскольку условие шаблона является всего лишь подстановочным знаком, оно извлекает все записи из таблицы. Кроме того, при написании регулярных выражений PostgreSQL следует учитывать еще один важный момент : оператор сопоставления с образцом всегда начинается с оператора « ^ » и заканчивается знаком « $ ». Эти два оператора отмечают начало и конец оператора регулярного выражения. Подводя итог рисунку, выражение можно реализовать как «^.*$» , используя подстановочные знаки для фильтрации всех записей.

Сопоставить регулярное выражение (с учетом регистра)

SELECT *
FROM Email
WHERE Email ~ 'sqlguide';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  1 | Pratibha | pratibha67@sqlguide.edu
  3 | Disha    | disha@sqlguide.edu
  5 | Srishti  | srishti@sqlguide.edu
  7 | Rytham   | rytham6@sqlguide.edu
(4 rows)

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

Сопоставить регулярное выражение (без учета регистра)

SELECT *
FROM Email
WHERE Email ~* 'SQLGUIDE';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  1 | Pratibha | pratibha67@sqlguide.edu
  3 | Disha    | disha@sqlguide.edu
  5 | Srishti  | srishti@sqlguide.edu
  7 | Rytham   | rytham6@sqlguide.edu
(4 rows)

В этом примере мы использовали оператор сопоставления «~*» для сопоставления регулярного выражения «SQLGUIDE» со столбцом «Электронная почта». В результате возвращаются все строки, содержащие шаблон «SQLGUIDE» с нечувствительностью к регистру.

Не соответствует регулярному выражению (с учетом регистра)

SELECT *
FROM Email
WHERE Email !~ 'sqlguide';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  2 | Bhavya   | bhavyaa@pythonguide.com
  4 | Divanshi | divanshi23@spguide.com
  6 | Kartik   | kartik@pythonguide.com
  8 | Madhav   | madhav@tsinfo.edu
  9 | Tanisha  | tanisha@pythonguide.com
 10 | Radhika  | radhika41@spguide.com
(6 rows)

В этом примере регулярное выражение «sqlguide» сопоставлялось с полем «Электронная почта» с помощью оператора сопоставления «!~». Результаты показали, что во всех строках отсутствует шаблон.

Не соответствует регулярному выражению (без учета регистра)

SELECT *
FROM Email
WHERE Email !~* 'sqlguide';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  2 | Bhavya   | bhavyaa@pythonguide.com
  4 | Divanshi | divanshi23@spguide.com
  6 | Kartik   | kartik@pythonguide.com
  8 | Madhav   | madhav@tsinfo.edu
  9 | Tanisha  | tanisha@pythonguide.com
 10 | Radhika  | radhika41@spguide.com
(6 rows)

На этой иллюстрации мы использовали оператор сопоставления «~*» для сопоставления регулярного выражения «sqlguide» со столбцом «Электронная почта». В результате были получены все строки, не содержащие шаблона «sqlguide» с CASE-нечувствительностью.

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

SELECT *
FROM Email
WHERE Email ~ '^d';
—--OUTPUT—--
id |   name   |         email          
----+----------+------------------------
  3 | Disha    | disha@sqlguide.edu
  4 | Divanshi | divanshi23@spguide.com
(2 rows)

 Например, если бы мы хотели найти имена, начинающиеся с буквы «d», мы бы использовали символ «^», который указывает начало/начало шаблона регулярного выражения.

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

SELECT *
FROM Email
WHERE Email ~ 'com$';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  2 | Bhavya   | bhavyaa@pythonguide.com
  4 | Divanshi | divanshi23@spguide.com
  6 | Kartik   | kartik@pythonguide.com
  9 | Tanisha  | tanisha@pythonguide.com
 10 | Radhika  | radhika41@spguide.com
(5 rows)

В этом примере знак «$», обозначающий конец шаблона регулярного выражения, использовался для обозначения того, что мы искали адреса электронной почты, заканчивающиеся на «com».

Соответствует данным числового типа

SELECT *
FROM Email
WHERE Email ~ '[0-9]';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  1 | Pratibha | pratibha67@sqlguide.edu
  4 | Divanshi | divanshi23@spguide.com
  7 | Rytham   | rytham6@sqlguide.edu
 10 | Radhika  | radhika41@spguide.com
(4 rows)

Используя регулярное выражение «[0-9]», мы показали в этом примере адреса электронной почты хотя бы с одним номером.

Соответствует данным числового типа (двузначное число).

SELECT *
FROM Email
WHERE Email ~ '[0-9][0-9]';
—--OUTPUT—--
id |   name   |          email          
----+----------+-------------------------
  1 | Pratibha | pratibha67@sqlguide.edu
  4 | Divanshi | divanshi23@spguide.com
 10 | Radhika  | radhika41@spguide.com
(3 rows)

Используя регулярное выражение «[0-9][0-9]», в этом примере мы показали результаты для тех адресов электронной почты, которые содержат не менее двух цифровых символов.

Функции и использование регулярных выражений PostgreSQL

В следующем разделе мы обсудим следующие функции и их использование. Давай начнем!

• Postgres regexp_replace
• Postgres regexp_replace Все вхождения
• Postgres regexp_matches в предложении WHERE
• Подстрока регулярного выражения Postgres
• Только числа регулярных выражений Postgres
• Разделение регулярных выражений Postgres
• Postgres Regex удалить специальные символы

Postgres regexp_replace

Чтобы заменить вхождение числового типа данных символом, воспользуемся функцией REGEXP_REPLACE():

SELECT REGEXP_REPLACE(Email,'[0-9]','*') 
From Email;
—--OUTPUT—--
    regexp_replace      
-------------------------
 pratibha*7@sqlguide.edu
 bhavyaa@pythonguide.com
 disha@sqlguide.edu
 divanshi*3@spguide.com
 srishti@sqlguide.edu
 kartik@pythonguide.com
 rytham*@sqlguide.edu
 madhav@tsinfo.edu
 tanisha@pythonguide.com
 radhika*1@spguide.com
(10 rows)

Вывод ясно показывает, что функция REGEXP_REPLACE() вставляет символ «*» вместо любого числового типа данных, который появляется в поле «Электронная почта». Эта функция заменяет первый экземпляр числового типа данных, как показано.

Postgres regexp_replace Все вхождения

SELECT REGEXP_REPLACE(Email,'[0-9]','*','g') 
From Email;
—--OUTPUT—--
  regexp_replace      
-------------------------
 pratibha**@sqlguide.edu
 bhavyaa@pythonguide.com
 disha@sqlguide.edu
 divanshi**@spguide.com
 srishti@sqlguide.edu
 kartik@pythonguide.com
 rytham*@sqlguide.edu
 madhav@tsinfo.edu
 tanisha@pythonguide.com
 radhika**@spguide.com
(10 rows)

Вывод ясно показывает, что функция REGEXP_REPLACE() используется для замены каждого экземпляра данных типа «Числовой» символом «*». Мы предоставили флаг «g», который ищет каждый экземпляр указанного шаблона.

Postgres regexp_matches в предложении WHERE

Когда регулярное выражение выполняется для строки, функция REGEXP_MATCHES() сравнивает их и возвращает строку, соответствующую шаблону, в виде набора. Для лучшего понимания мы рассмотрим пример функции REGEXP_MATCHES().

SELECT REGEXP_MATCHES(Email,'.+@(.*)$')
FROM Email;
—--OUTPUT—--
regexp_matches   
-------------------
 {sqlguide.edu}
 {pythonguide.com}
 {sqlguide.edu}
 {spguide.com}
 {sqlguide.edu}
 {pythonguide.com}
 {sqlguide.edu}
 {tsinfo.edu}
 {pythonguide.com}
 {spguide.com}
(10 rows)

В этом случае мы искали доменное имя адреса электронной почты с помощью функции REGEXP_MATCHES(). Функция REGEXP_MATCHES() вернула результаты в виде набора.

Подстрока регулярного выражения Postgres

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

SELECT DISTINCT SUBSTRING(Email FROM '.+@(.*)$') 
FROM Email;
—--OUTPUT—--
 substring    
-----------------
 sqlguide.edu
 spguide.com
 pythonguide.com
 tsinfo.edu
(4 rows)

Здесь мы использовали функцию SUBSTRING() для извлечения доменного имени адреса электронной почты из таблицы Email с использованием ключевого слова DISTINCT, которое отображает только отдельные имена.

Только числа регулярных выражений Postgres

Здесь мы будем использовать функцию REGEXP_REPLACE() для извлечения только чисел из строки в PostgreSQL.

Давайте рассмотрим пример извлечения вхождений числовых типов данных.

SELECT REGEXP_REPLACE(Email,'\D','','g')
FROM Email;
—--OUTPUT—--
regexp_replace 
----------------
 67
 23
 6
 41
(10 rows)

Из полученных результатов видно, что мы извлекли только данные числового типа из поля «Электронная почта» в PostgreSQL с помощью функции REGEXP_REPLACE(). Здесь «\D» — это сокращение класса «Not a Digit».

Разделение регулярных выражений Postgres

В PostgreSQL функция SPLIT_PART() может разбить строку на множество частей. Чтобы разделить строку на несколько частей, мы должны передать строку, разделитель и номер файла. Синтаксис функции PostgreSQL SPLIT_PART() следующий:

SPLIT_PART(STRING,DELIMITER,FIELD_NUMBER)

Случай №1

SELECT SPLIT_PART(Email,'@',1)
FROM Email;
—--OUTPUT—--
split_part 
------------
 pratibha67
 bhavyaa
 disha
 divanshi23
 srishti
 kartik
 rytham6
 madhav
 tanisha
 radhika41
(10 rows)

Случай №2

SELECT SPLIT_PART(Email,'@',2)
FROM Email;
—--OUTPUT—--
  split_part    
-----------------
 sqlguide.edu
 pythonguide.com
 sqlguide.edu
 spguide.com
 sqlguide.edu
 pythonguide.com
 sqlguide.edu
 tsinfo.edu
 pythonguide.com
 spguide.com
(10 rows)

В этом примере поле «Электронная почта» таблицы «Электронная почта» было разделено на две части с помощью разделителя «@» и функции SPLIT_PART(). Он возвращает текст перед разделителем, когда мы указываем НОМЕР ПОЛЯ как «1» в случае 1, и текст после разделителя, когда мы передаем НОМЕР ПОЛЯ как «2» в случае 2.

Postgres Regex удалить специальные символы

Удалите специальные символы с помощью PostgreSQL Regex. Используя функцию REGEXP_REPLACE(), можно удалить все специальные символы из предоставленного текста. Мы рассмотрим пример, в котором мы используем функцию REGEXP_REPLACE() PostgreSQL для исключения каждого экземпляра специального символа из столбца таблицы.

SELECT REGEXP_REPLACE(Email, '[^\w]+','','g')
FROM Email;
—--OUTPUT—--
   regexp_replace     
-----------------------
 pratibha67sqlguideedu
 bhavyaapythonguidecom
 dishasqlguideedu
 divanshi23spguidecom
 srishtisqlguideedu
 kartikpythonguidecom
 rytham6sqlguideedu
 madhavtsinfoedu
 tanishapythonguidecom
 radhika41spguidecom
(10 rows)

В этом примере показано, что функция REGEXP_REPLACE() использовалась для исключения любых экземпляров специальных символов.

Преимущества регулярного выражения PostgreSQL

Преимущества работы с PostgreSQL Regex перечислены ниже:

• Функции PostgreSQL Regex поддерживают множество флагов. Например: REGEXP_MATCHES() поддерживает:

Флаг 'i': совпадение без учета регистра.

Флаг «g»: глобальный поиск каждого вхождения.

• Функция PostgreSQL Regex не возвращает ни одной строки, одну строку или несколько строк по определенному шаблону.
• Функция PostgreSQL Regex может возвращать ноль, одну или несколько строк в зависимости от указанного шаблона.
• Функции PostgreSQL Regex позволяют нам искать любое слово в нужной строке в любой позиции или просто искать по первому вхождению.
• Мы также можем извлечь значения столбцов таблицы с помощью PostgreSQL Regex.
• Функции PostgreSQL Regex можно использовать в целях проверки достоверности.
• Это позволяет нам выполнять сложные операции, такие как поиск строк с использованием случайных символов.
• По сравнению со сложной смесью операторов регистра и подстрок PostgreSQL Regex более универсален и работает лучше.

Заключение

В этом руководстве мы подробно рассмотрели, как использовать регулярные выражения для написания операторов SQL в PostgreSQL . Мы можем создавать динамические SQL-запросы, которые используют регулярные выражения для сопоставления шаблонов из столбца базы данных. Оператор SQL LIKE в PostgreSQL также может сопоставлять шаблоны, но поиск более ограничен.

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