Regular expression matching (~ operator) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ~ operator do in PostgreSQL?

The ~ operator checks if a string matches a regular expression pattern. It returns true if the pattern matches, false otherwise.

beginner

How do you write a query to find rows where the column name contains only digits using ~?

Use WHERE name ~ '^\d+$'. This means the entire name must be digits from start (^) to end ($).

intermediate

What is the difference between ~ and ~* operators in PostgreSQL?

~ is case-sensitive regex match, while ~* is case-insensitive regex match.

intermediate

Write a PostgreSQL query using ~ to find emails ending with @example.com.

SELECT * FROM table WHERE email ~ '@example\.com$'; <br> The \. escapes the dot, and $ means end of string.

intermediate

Can the ~ operator be used to check for multiple patterns at once?

No, ~ matches one regex pattern at a time. To check multiple patterns, combine conditions with OR or use regex alternation like pattern1|pattern2.

What does the PostgreSQL operator ~ do?

A Concatenates two strings

B Performs a case-insensitive string comparison

C Checks if a string contains only letters

D Matches a string against a regular expression pattern

The ~ operator matches a string against a regex pattern and returns true if it matches.

Which operator would you use for case-insensitive regex matching in PostgreSQL?

A ~*

B ~

C !~

D ILIKE

~* is the case-insensitive regex match operator in PostgreSQL.

What does the regex pattern ^\d+$ mean when used with ~?

A String contains at least one digit

B String contains only digits from start to end

C String ends with a digit

D String starts with a digit

The pattern means the entire string (from ^ start to $ end) consists only of digits (\d+).

How do you escape a dot (.) in a regex pattern for PostgreSQL?

A \.

B .

C \d

D \*

A dot means any character in regex, so to match a literal dot, you escape it with a backslash: \.

Can the ~ operator match multiple patterns separated by commas?

A Yes, it matches all patterns automatically

B Yes, but only if patterns are in quotes

C No, it matches only one regex pattern at a time

D No, it only matches numeric patterns

~ matches one regex pattern at a time. To match multiple patterns, use alternation or multiple conditions.

Explain how the ~ operator works in PostgreSQL and give an example query.

Think about how you check if a string fits a pattern using regex.

Describe the difference between ~ and ~* operators and when to use each.

Consider if letter case matters in your pattern matching.