Regular Expressions in SQL

Regular expressions (regex) are powerful tools used to search, match, extract and replace text based on specific patterns. In SQL, regex helps manage and manipulate textual data more efficiently than simple string functions and makes it useful for handling complex data-processing tasks.

Example:

SELECT email 
FROM users 
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

This query retrieves all emails from the users table that match the regex pattern for valid email addresses.

Types of Regular Expressions

In SQL, there are three primary functions for working with regular expressions:

REGEXP_LIKE

REGEXP_LIKE checks whether a string matches a given regular expression and returns TRUE or FALSE. It is commonly used in WHERE clauses to filter rows that match a specific text pattern.

Syntax:

REGEXP_LIKE(column_name, 'pattern')

Query:

The following query selects all product names from the products table where the names start with the letter 'A':

SELECT product_name 
FROM products 
WHERE REGEXP_LIKE(product_name, '^A');

Output:

REGEXP_REPLACE

REGEXP_REPLACE in SQL is used to find a pattern in a string and replace it with another value. It helps clean and format data by removing or changing unwanted characters.

Syntax:

REGEXP_REPLACE(string, 'pattern', 'replacement')

Query:

The following query removes all non-numeric characters from the phone_number column in the contacts table:

SELECT REGEXP_REPLACE(phone_number, '[^0-9]', '') AS cleaned_number 
FROM contacts;

Output:

REGEXP_SUBSTR

REGEXP_SUBSTR extracts a part of a string that matches a given regular expression, making it useful for pulling specific patterns like emails or numbers.

Syntax:

REGEXP_SUBSTR(string, 'pattern', start_position, occurrence, match_parameter)

Query:

To extract the domain name from the email field in the users table:

SELECT REGEXP_SUBSTR(email, '@[^.]+') AS domain 
FROM users;

Output:

Basic Regular Expression Syntax Table

The following table lists common regex symbols, their meanings and examples:

Common REGEX Patterns