Checks whether a string matches a regular expression.
REGEXP_LIKE(<text>, '<pattern>')
REGEXP_LIKE(<text>, '<pattern>', '<flags>')
<text> ~ '<pattern>'
<text> ~* '<pattern>'
Returns true or false. Unlike LIKE with its % and _, the pattern is a full regular expression: '^[0-9]+$' — digits only, '@' — there's an at sign somewhere. The 'i' flag turns off case sensitivity. The operators ~ and ~* (case-insensitive) do the same.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| only_digits | with_letter |
|---|---|
| true | false |
| name | phone | valid_format |
|---|---|---|
| Emma | +44 7700 900123 | true |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | true |
| Noah | +44 7700 900789 | true |
| Sophia | NULL | NULL |
\+ is the plus sign itself, [0-9]{4} — exactly four digits.
| name |
|---|
| Emma |
~* ignores case, so the capital E in Emma matches [a-e].
The pattern looks for a match anywhere in the string: REGEXP_LIKE('abc123', '[0-9]') gives true. To check the whole string, add ^ at the start and $ at the end.
Plus signs, dots and brackets have special meanings. To match the character itself, put a backslash in front of it: '\+'.
REGEXP_LIKE exists only since PostgreSQL 15. In older versions, use the ~ operator.