SIMILAR TO for regex-lite matching in PostgreSQL

Introduction

Use SIMILAR TO to check if text matches a simple pattern using special symbols. It helps find text that looks like what you want without full complex rules.

You want to find names that start with 'A' and end with 'n'.

You need to check if a code has digits followed by letters.

You want to filter emails that have a certain pattern like '[e-mail]'.

You want to find words that contain either 'cat' or 'dog'.

Syntax

PostgreSQL

expression SIMILAR TO pattern

The pattern uses % for any sequence of characters and _ for any single character.

You can use | to mean OR between patterns inside parentheses.

Examples

Checks if 'apple' starts with 'a' followed by anything.

PostgreSQL

'apple' SIMILAR TO 'a%'

Checks if text has one or more digits followed by one or more letters.

PostgreSQL

'123abc' SIMILAR TO '[0-9]+[a-z]+'

Checks if text is exactly 'cat' or 'dog'.

PostgreSQL

'dog' SIMILAR TO '(cat|dog)'

Sample Program

This runs four checks: if 'apple' starts with 'a', if '123abc' has digits then letters, if 'dog' is 'cat' or 'dog', and if 'bat' is 'cat' or 'dog'.

PostgreSQL

SELECT 'apple' SIMILAR TO 'a%';
SELECT '123abc' SIMILAR TO '[0-9]+[a-z]+';
SELECT 'dog' SIMILAR TO '(cat|dog)';
SELECT 'bat' SIMILAR TO '(cat|dog)';

Important Notes

SIMILAR TO is simpler than full regular expressions but more powerful than LIKE.

Use SIMILAR TO when you want basic pattern matching with some regex features.

Remember to use single quotes around patterns.

Summary

SIMILAR TO matches text against simple patterns with % and _ wildcards.

It supports basic regex features like +, |, and character classes.

It is useful for quick pattern checks without full regex complexity.