PostgreSQL: Like Operator

Introduction

There are three types of pattern matching in PostgreSQL : LIKE operator, SIMILAR TO operator, and POSIX-style regular expressions. The LIKE expression returns true if the string matches the supplied pattern. and the NOT LIKE expression returns false if LIKE returns true.

Syntax:

string LIKE pattern [ESCAPE escape-character] string 
NOT LIKE pattern [ESCAPE escape-character]

Pictorial Presentation of PostgreSQL Like Operator

Example:

'xyz' LIKE 'xyz'    true
'xyz' LIKE 'x%'     true
'xyz' LIKE '_y_'    true
'xyz' LIKE 'z'      false

Using the Like operator

• LIKE pattern matching always covers the entire string. Therefore to match a sequence anywhere within a string, the pattern must start and end with a percent sign.
• Search conditions can contain either literal characters or numbers: An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

Here is sample table employees

Example:

If we want to extract those rows which contain the first_name starting with the letter 'M' from employees table, the following statement can be used.

postgres=# SELECT first_name
postgres-# FROM employees
postgres-# WHERE first_name LIKE'M%';
 first_name
------------
 Matthew
 Mozhe
 Michael
 Mattea
 Martha
 Michael
(6 rows)

Example:

If we want to extract those rows which contain the first_name ending with the letter 'h' from employees table , the following statement can be used.

postgres=# SELECT first_name
postgres-# FROM employees
postgres-# WHERE first_name LIKE'%h';
 first_name
------------
 Sarath
 Elizabeth
 Sarah
(3 rows))

Example:

If we want to extract those rows which contain the first_name second most starting with the letter 'h' from employees table , the following statement can be used.

postgres=# SELECT last_name
postgres-# FROM employees
postgres-# WHERE last_name LIKE'_h%';
 last_name
------------
 Chen
 Khoo
 Philtanker
 Chung
 Whalen
(5 rows)

Example:

If we want to extract those rows which contain the first_name with the letter 'y' in any position from employees table , the following statement can be used.

postgres=# SELECT first_name
postgres-# FROM employees
postgres-# WHERE first_name LIKE'%y%';
 first_name
------------
 Nancy
 Guy
 Payam
 Lindsey
 Tayler
 Alyssa
 Kimberely
 Anthony
 Kelly
 Timothy
 Britney
 Shelley
(12 rows)

Example:

If we want to extract those rows which contain the first_name only with 9 characters from employees table , the following statement can be used.

postgres=# SELECT first_name
postgres-# FROM employees
postgres-# WHERE first_name LIKE'_________';
 first_name
------------
 Alexander
 Alexander
 Elizabeth
 Kimberely
(4 rows)

Previous: Bit String Functions and Operators
Next: Trigonometric Functions