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.

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

Press + to interact
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 ( ...