Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Regular Expressions in SQL

Regular Expressions in SQL

Learn about regular expressions in PostgreSQL, including how to use the REGEXP_MATCH and REGEXP_REPLACE functions, as well as various metacharacters.

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

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 ( ...