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).
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:
LIKESIMILAR 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:
~: Case-sensitive, compares two statements, returns true if the first string is contained in the second~*: Case-insensitive, compares two statements, returns true if the first string is contained in the second!~: Case-sensitive, compares two statements, returns false if the first string is contained in the second!~*: 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:
LIKESIMILAR 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';
To get all entries that start with the word ‘Fire’:
SELECT (column name)
FROM (table name)
WHERE (column name) ~ * '^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