Close

How Regex in SQL works

Last modified: December 09, 2019


What is Regex?


Regex, or Regular Expressions, is a sequence of characters, used to search and locate specific sequences of characters that match a pattern.

In SQL if you were looking for email addresses from the same company Regex lets you define a pattern using comparators and Metacharacters, in this case using ~* and % to help define the pattern:

SELECT * FROM Email Addresses
WHERE Email Address ~* '%@chartio.com'

2. Run the following query to generate the GRANT statements for your restricted user. Replace ‘mydatabase,’ ‘myuser,’ and ‘myhost’ with specific information for your database.

Note that the quotations surrounding myuser and mypassword are two single quotes, not double. The characters surrounding myhost and ,TABLE_NAME, are backticks (the key is located under the escape key on your keyboard).

Regex Search Gif

Using Regex in PostgreSQL


Metacharacters

Here is a quick cheat sheet for metacharacters to help define the pattern:

METACHARACTER

DESCRIPTION

EXAMPLE

EXAMPLES MATCHES

^

DESCRIPTION

Start the match at the beginning of a string

EXAMPLE

^c%

EXAMPLES MATCHES

cat, car, chain

|

DESCRIPTION

Alternation (either of two alternatives)

EXAMPLE

c(a|o)%

EXAMPLES MATCHES

can, corn, cop

()

DESCRIPTION

Group items in a single logical item

EXAMPLE

c(a|o)%

EXAMPLES MATCHES

can, corn, cop

_

DESCRIPTION

Any single character (using LIKE and SIMILAR TO)

EXAMPLE

c_

EXAMPLES MATCHES

co, fico, pico

%

DESCRIPTION

Any string (using LIKE and SIMILAR TO)

EXAMPLE

c%

EXAMPLES MATCHES

chart, articulation, crate

.

DESCRIPTION

Any single character (using POSIX)

EXAMPLE

c.

EXAMPLES MATCHES

co, fico, pico

.*

DESCRIPTION

Any string (using POSIX)

EXAMPLE

c.*

EXAMPLES MATCHES

chart, articulation, crate

+

DESCRIPTION

Repetition of the previous item one or more times

EXAMPLE

co+

EXAMPLES MATCHES

coo, cool

Comparators


There are three ways to use regex comparisons in SQL:

  • LIKE
  • SIMILAR TO
  • POSIX comparators

LIKE and SIMILAR TO are used for basic comparisons where you are looking for a matching string. LIKE and SIMILAR TO both look and compare string patterns, the only difference is that SIMILAR TO uses the SQL99 definition for regular expressions and LIKE uses PSQL’s definition for regular expressions.

Syntax: [String or Column name] LIKE/SIMILAR TO [Regex]

EXPRESSION

RETURNS

‘char’ LIKE ‘char’

True

‘char’ LIKE ‘c%’

True

‘char’ LIKE ‘ha’

True

‘char’ LIKE ‘c’

False

Unlike LIKE and SIMILAR TO, POSIX is not a keyword that is used in a SQL query. POSIX is a set of comparators for case matches and non equivalency. It is the most powerful way to use Regex in SQL. Regex does not use = and != to compare rather it uses these POSIX comparators:

  1. : Case-sensitive, compares two statements, returns true if the first string is contained in the second
  2. ~* : Case-insensitive, compares two statements, returns true if the first string is contained in the second
  3. !~ : Case-sensitive, compares two statements, returns false if the first string is contained in the second
  4. !~* : Case-insensitive, compares two statements, return false if the first string is contained in the second

Syntax: [String or Column name] [POSIX] [Regex]

These comparators can be used in queries to locate or exclude certain data from being returned.

Examples of Regex in SQL Queries


There are three ways to use regex comparisons in SQL:

  • LIKE
  • SIMILAR TO
  • POSIX comparators

LIKE and SIMILAR TO are used for basic comparisons where you are looking for a matching string. LIKE and SIMILAR TO both look and compare string patterns, the only difference is that SIMILAR TO uses the SQL99 definition for regular expressions and LIKE uses PSQL’s definition for regular expressions.

Syntax: [String or Column name] LIKE/SIMILAR TO [Regex]

Operator

Description

Comparisons

Output

~

Description

Match, Case Sensitive

Comparisons

'Timmy' ~ 'T%'

Output

True

Comparisons

'Timmy' ~ 'T%'

Output

False

~*

Description

Match, not Case Sensitive

Comparisons

'Timmy' ~ 'T%'

Output

True

Comparisons

'Timmy' ~ 'T%'

Output

False

!~

Description

No Match, Case Sensitive

Comparisons

'Timmy' ~ 'T%'

Output

True

Comparisons

'Timmy' ~ 'T%'

Output

False

!~*

Description

No Match, not Case Sensitive

Comparisons

'Timmy' ~ 'T%'

Output

True

Comparisons

'Timmy' ~ 'T%'

Output

False

If you wanted to search a column of a database for all entries that contain the word ‘fire’, you could use ~* ‘fire’  to find any row that contains the word:

SELECT (column name)
FROM (table name)
WHERE (column name) ~* 'fire';
All songs with Fire in the title

To get all entries that start with the word ‘Fire’:

SELECT (column name)
FROM (table name)
WHERE (column name) ~ * '^fire';
Just songs that start with Fire

A full list of regular expressions can be found at: RexEgg

Summary


  • Regular expressions use patterns to match strings.
  • Regex provides a way to query databases to find a smaller subset of data.
  • The POSIX comparators are:
  • ~ : Case-sensitive, compares two statements, returns true if the first is contained in the second
  • ~* : Case-insensitive, compares two statements, returns true if the first is contained in the second
  • !~ : Case-sensitive, compares two statements, returns false if the first is contained in the second
  • !~* : Case-insensitive, compares two statements, return false if the first is contained in the second