SIMILAR TO for regex-lite matching in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SIMILAR TO operator do in PostgreSQL?

It checks if a string matches a pattern using a simplified form of regular expressions, combining SQL LIKE and regex features.

intermediate

How is SIMILAR TO different from LIKE in PostgreSQL?

LIKE uses simple wildcard patterns (% and _), while SIMILAR TO supports more complex patterns like alternation (|) and grouping with parentheses.

beginner

Write a SIMILAR TO pattern to match strings that start with 'cat' or 'dog'.

Use '(cat|dog)%' inside SIMILAR TO. For example: WHERE animal SIMILAR TO '(cat|dog)%' matches strings starting with 'cat' or 'dog'.

beginner

What symbols are used in SIMILAR TO for alternation and grouping?

Alternation uses the pipe symbol |, and grouping uses parentheses ( ).

intermediate

Can SIMILAR TO patterns use quantifiers like + or *?

No, SIMILAR TO supports only a limited set of regex features. Quantifiers like + or * are not supported; use % and _ for repetition instead.

Which operator in PostgreSQL allows pattern matching with a simplified regex syntax?

A BETWEEN

B LIKE

C ILIKE

D SIMILAR TO

SIMILAR TO supports regex-lite pattern matching, unlike LIKE or ILIKE which use simpler wildcards.

What symbol does SIMILAR TO use for alternation (choice between patterns)?

A %

B *

C |

D _

The pipe | symbol is used for alternation in SIMILAR TO patterns.

Which of these patterns matches strings starting with 'cat' or 'dog' using SIMILAR TO?

A '(cat|dog)%'

B 'cat|dog%'

C 'cat%|dog%'

D 'cat|dog'

Parentheses group the alternatives, so '(cat|dog)%' matches strings starting with 'cat' or 'dog'.

Can you use + quantifier in SIMILAR TO patterns?

A Yes, always

B No, never

C Only with escape characters

D Only in PostgreSQL 14+

SIMILAR TO does not support regex quantifiers like + or *.

Which wildcard in SIMILAR TO matches any sequence of characters?

A %

B +

C *

D _

The percent sign % matches any sequence of characters in SIMILAR TO patterns.

Explain how the SIMILAR TO operator works in PostgreSQL and how it differs from LIKE.

Think about how <code>SIMILAR TO</code> combines SQL and regex features.

Write an example SQL query using SIMILAR TO to find rows where a column starts with 'red' or 'blue'.

Use <code>WHERE column SIMILAR TO '(red|blue)%'</code>.