How to use regular expressions in PostgreSQL with examples

Introduction

You may know already about the power of regular expressions in programming. It iis doing a great job in DBMS also. For me it is very helpful in PostgreSQL. I am sure you can use Regex in some other DBMSs also such as MySQL.

Where can use it?

Can be use it as alternate of ILIKE or LIKE.
Can be use it the places to find similar string matches or partial string matches. Of course real replacement of field_name ILIKE ‘%asdf%’

Why can use it?

It will reduce the scripting execution time to 50% if you use it properly.
Easy to write queries. Time saving.

How to use it?

The Tilde Operator:

The tilde operator returns true or false depending on whether a regular expression can match part of a string, or not.

E.g.
‘subject’ ~ ‘regexp’ returns false,
‘subject’ ~ ‘\w’ returns true.

If the regex must match the whole string, you’ll need to use anchor characters. To know about anchor characters in Regular Expressions, you can refer my another post.

E.g.
‘subject’ ~ ‘^\\w$’ returns false
‘subject’ ~ ‘^\\w+$’ returns true.

There are 4 variations of tilde operator:

~ attempts a case sensitive match
~* attempts a case insensitive match
!~ attempts a case sensitive match, and returns true if the regex does not match any part of the subject string
!~* attempts a case insensitive match, and returns true if the regex does not match any part of the subject string

Some Comparisons for the RegeX performance:

In this example regular expression have more efficiency than ILIKE

test=# select * from products where code ilike '000112';
name | code
------------+--------
ABC Grade1 | 000112
(1 row)

Time: 0.697 ms

test=# select * from products where code ~ '^000112$';
name | code
------------+--------
ABC Grade1 | 000112
(1 row)

Time: 0.464 ms

Some other usages

test=# select * from products
test-# ;
name | code
-------------+-----------
ABC Grade1 | 000112
ABC Grade2 | 000122
ABC-Grade 1 | 112
ABC Grade-1 | 000112X
abc grade-1 | 000112abc
(5 rows)

Time: 0.472 ms

test=# select * from products where code ~ '(^0*?)112$';
name | code
-------------+--------
ABC Grade1 | 000112
ABC-Grade 1 | 112
(2 rows)

Time: 0.541 ms

Searching records that have code match the string “112” and that may have N number of zeros or non zeros and any number of albhabets appended.

test=# select * from products where code ~ '(^0*?)112(?![0-9])';
name | code
-------------+-----------
ABC Grade1 | 000112
ABC-Grade 1 | 112
ABC Grade-1 | 000112X
abc grade-1 | 000112abc
(4 rows)

Time: 0.615 ms

And negative matches for the above case. Similar to NOT ILIKE.

test=# select * from products where code !~ '(^0*?)112(?![0-9])';
name | code
------------+--------
ABC Grade2 | 000122
(1 row)

Time: 0.580 ms

Case insensitive pattern matching.

test=# select * from products where code ~* '112x';
name | code
-------------+---------
ABC Grade-1 | 000112X
(1 row)

Time: 0.563 ms

Case sensitive pattern matching.

test=# select * from products where code ~ '112x';
name | code
------+------
(0 rows)

Time: 0.568 ms

Cover Image: PostgreSQL

CodeForLearning