Search⌘ K
Home
Courses
Mastering PostgreSQL Databases: From Basics to Advanced

Regular Expressions in SQL

Explore how to apply regular expressions in PostgreSQL SQL commands like SELECT, UPDATE, and DELETE. Understand usage of operators such as tilde and SIMILAR TO for effective pattern matching and text processing in your database queries.

We'll cover the following...

Introduction

Regular expressions, also known as regex or regexp, are powerful tools for manipulating text and data. In PostgreSQL, they can be used in the following commands:

  • The SELECT statements

  • The UPDATE statements

  • The DELETE statements

PostgreSQL
SELECT
  <columns>
FROM
  <table>
WHERE
  <column> ~ 'pattern';
UPDATE
  <table>
SET
  <column> = REGEXP_REPLACE(< column >, 'pattern', 'replacement');
DELETE FROM
  <table>
WHERE
  <column> ~ 'pattern';

Here, <columns> is the column(s) we want to return, and <table> is the table we want to search, update, or delete. The <column> represents the column containing the string to search for a match. The tilde ( ...