PostgreSQL LIKE condition

Preamble

PostgreSQL LIKE condition allows using wildcards (metacharacters) in WHERE proposal of SELECT, INSERT, UPDATE or DELETE operator. This allows for pattern matching.

The syntax for the LIKE condition in PostgreSQL

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters and arguments of the LIKE condition

• expression – a symbolic expression, such as a field or a column.
• pattern – Symbolic expression that contains the matching pattern. Templates that you can choose from:

• escape_character – Optional. This allows you to check for alphabetic characters such as % or _. If you do not provide escape_character, PostgreSQL assumes that \ is escape_character.

An example using the % wildcard (percent character)

The first PostgreSQL LIKE example we will look at involves the use of the % wildcard (percentage character).

Let’s look at how the % wildcard works in PostgreSQL LIKE. We want to find all employees, last_name starts with ‘Jo’.

SELECT *
FROM employees
WHERE first_name LIKE 'Jo%';

You can also use the % wildcard multiple times on the same line. For example:

SELECT *
FROM employees
WHERE first_name LIKE '%od%';

In this PostgreSQL example of the LIKE condition, we look for all employees whose first_name contains the ‘od’ character.

Example using the wildcard _ (underscore character)

Next, let’s look at how the _ (underscore character) wildcard works in PostgreSQL LIKE. Remember that the wildcard _ only looks for one character.
For example:

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'Yoh_n';

This example of PostgreSQL condition LIKE would return all suppliers whose supplier_name is 5 characters long, where the first three characters are “Yoh” and the last one is “n”. For example, it could return table entries where the first_name is “Yohan”, “Yohen”, “Yohin”, “Yohon” etc.

Here is another example:

SELECT *
FROM employees
WHERE employee_number LIKE '98765_';

You may find that you are looking for an account number, but you only have 5 of 6 digits. In the above example you could get back 10 entries (where the missing value could be 0-9).

For example, it could return the table entries with employee_number:

987650, 987651, 987652, 987653, 987654, 987655, 987656, 987657, 987658, 987659

Example using the NOT operator

Now let’s see how you can use the NOT operator with wildcards.
Let’s use the % wildcard with the NOT operator. You can also use the PostgreSQL condition LIKE to find table entries whose last_name does not start with ‘J’.
For example:

SELECT first_name, last_name
FROM employees
WHERE last_name NOT LIKE 'J%';

By placing the NOT operator before the PostgreSQL condition LIKE, you can get all records whose last_name does not start with ‘J’.

Example using Escape-symbols

It is important to understand how to “screen the characters” when the pattern matches. These examples deal with character escaping in PostgreSQL.

Let’s say you wanted to find the % or _ character in the PostgreSQL LIKE condition. You can do this with the Escape character.

Note that you can only define the escape-character as one character (length 1).
For example:

SELECT *
FROM employees
WHERE last_name LIKE 'G\%';

Since we did not specify an escape-symbol, PostgreSQL assumes that \ is an escape-symbol. PostgreSQL then assumes that the escape-symbol is \, so PostgreSQL treats % as a literal instead of a wildcard. This operator then returns all records from employees whose last_name is G%.

We can override the default escape-symbol in PostgreSQL by providing the ESCAPE modifier as follows:

SELECT *
FROM employees
WHERE last_name LIKE 'G!%' ESCAPE '!';

This example PostgreSQL condition LIKE defines ! as an escape-symbol. ! the escape-symbol will cause PostgreSQL to treat % as a literal. As a result, this operator will also return all records from the Employees table, the last_name of which is G%.

Here is another more complex example of using escape-characters in PostgreSQL under the LIKE condition.

SELECT *
FROM employees
WHERE last_name LIKE 'M%\%';

This example PostgreSQL condition LIKE returns all employees whose last_name starts with ‘M’ and ends with ‘%’. For example, it would return a value such as ‘Mathison%’.

Since we didn’t specify an escape-character in the LIKE condition, PostgreSQL assumes that the escape character is \, which causes PostgreSQL to treat the second % character as a literal instead of a wildcard.
We could change this LIKE condition by specifying the escape-character as follows:

SELECT *
FROM employees
WHERE last_name LIKE 'M%!%' ESCAPE '!';

This example PostgreSQL condition LIKE returns all records from employees whose last_name starts with ‘M’ and ends with a literal ‘%’. For example, it will return a value such as “Mathison%”.

You can also use an escape-symbol with _ in the PostgreSQL LIKE condition.
For example:

SELECT *
FROM employees
WHERE last_name LIKE 'M%\_';

Again, since the ESCAPE modifier is not provided, PostgreSQL uses \ as an escape-symbol, resulting in _, which will be treated as a literal instead of a wildcard.

In this example, all fields from employees whose last_name starts with ‘M’ and ends with ‘_’ will be returned. For example, it would return a value such as ‘Mathison_’.

PostgreSQL: Like And iLike | Course

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.