Product Documentation

Appearance

Simple Regex Examples ​

The following examples are very simple regular expressions.

This example finds strings that contain "pool" (lowercase):

premdb=# SELECT name FROM team WHERE name ~ 'pool';
   name    
-----------
 Blackpool
 Liverpool
(2 rows)

The following example finds strings that contain "POOL" (case-insensitive):

premdb=# SELECT name FROM team WHERE name ~* 'POOL';
   name    
-----------
 Blackpool
 Liverpool
(2 rows)

The following example finds strings that do not contain "a" or "e":

premdb=# SELECT name FROM team WHERE name !~* 'a|e';
    name     
--------------
 Hull City
 Ipswich Town
 Norwich City
 Portsmouth
 Swindon Town
(5 rows)

The following example finds strings that contain "pool," "ham," or "ton":

premdb=# SELECT name FROM team WHERE name ~ 'pool|ham|ton';
         name           
-------------------------
 Aston Villa
 Birmingham City
 Blackpool
 Bolton Wanderers
 Charlton Athletic
 Everton
 Fulham
 Liverpool
 Nottingham Forest
 Oldham Athletic
 Southampton
 Tottenham Hotspur
 Wolverhampton Wanderers
(13 rows)

The following example finds strings that contain "ee":

premdb=# SELECT name FROM team WHERE name ~ 'e{2}';
       name         
---------------------
 Leeds United
 Queens Park Rangers
(2 rows)

The following examples finds strings that contain "200" followed by a single character:

premdb=# select * from season where season_name ~ '200.';
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       8 | 1999-2000   |       20 | Manchester United
       9 | 2000-2001   |       20 | Manchester United
      10 | 2001-2002   |       20 | Arsenal
      11 | 2002-2003   |       20 | Manchester United
...

The following example finds strings that contain a "3":

premdb=# select * from season where season_name ~ '3';
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       1 | 1992-1993   |       22 | Manchester United
       2 | 1993-1994   |       22 | Manchester United
      11 | 2002-2003   |       20 | Manchester United
      12 | 2003-2004   |       20 | Arsenal
      21 | 2012-2013   |       20 | Manchester United
      22 | 2013-2014   |       20 | Manchester City
(6 rows)

Parent topic:SQL Operators and Pattern Matching Functions