Lesson 5 of 13 · 22 min
Pattern matching with regular expressions
By the end of this lesson you can write anchored regular expressions to validate text, find values that break a format, and extract or replace parts of strings with PostgreSQL's regex functions.
A regular expression (regex) is a small pattern language for describing the shape of text. PostgreSQL matches a string against a pattern with four operators:
| operator | meaning |
|---|---|
s ~ 'pattern' | matches, case-sensitive |
s ~* 'pattern' | matches, ignoring case |
s !~ 'pattern' | does not match, case-sensitive |
s !~* 'pattern' | does not match, ignoring case |
Unlike LIKE, a regex does not have to match the whole string: 'WELCOME10' ~ '10' is TRUE because '10' appears somewhere. To describe the whole value, you anchor the pattern.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons