Introduction to PostgreSQL LIKE operator

Suppose that you want to find customers, but you don’t remember their names exactly. However, you can recall that their names begin with something likeJen.

How do you locate the exact customers from the database? You can identify customers in the customer table by examining the first name column to see if any values begin withJen. However, this process can be time-consuming, especially when thecustomer table has a large number of rows.

Fortunately, you can use the PostgreSQLLIKE operator to match the first names of customers with a string using the following query:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name LIKE 'Jen%';

Output:

first_name | last_name------------+----------- Jennifer   | Davis Jennie     | Terry Jenny      | Castro(3 rows)

TheWHERE clause in the query contains an expression:

first_name LIKE 'Jen%'

The expression consists of thefirst_name, theLIKE operator and a literal string that contains a percent sign (%). The string'Jen%' is called a pattern.

The query returns rows whose values in thefirst_name column begin withJen and are followed by any sequence of characters. This technique is called pattern matching.

You construct a pattern by combining literal values with wildcard characters and using theLIKE orNOT LIKE operator to find the matches.

PostgreSQL offers two wildcards:

• Percent sign (%) matches any sequence of zero or more characters.
• Underscore sign (_)  matches any single character.

Here’s the basic syntax of theLIKE operator:

value LIKE pattern

TheLIKE operator returnstrue if thevalue matches thepattern. To negate theLIKE operator, you use theNOT operator as follows:

value NOT LIKE pattern

TheNOT LIKE operator returnstrue when thevalue does not match thepattern.

If the pattern does not contain any wildcard character, theLIKE operator behaves like the equal (=) operator.

PostgreSQL LIKE operator examples

Let’s take some examples of using theLIKE operator

1) Basic LIKE operator examples

The following statement uses theLIKE operator with a pattern that doesn’t have any wildcard characters:

SELECT 'Apple' LIKE 'Apple' AS result;

Output:

result-------- t(1 row)

In this example, theLIKE operator behaves like the equal to (=) operator. The query returnstrue because ‘Apple' = 'Apple' istrue.

The following example uses theLIKE operator to match any string that starts with the letterA:

SELECT 'Apple' LIKE 'A%' AS result;

Output:

result-------- t(1 row)

The query returns true because the string'Apple' starts with the letter'A'.

2) Using the LIKE operator with table data

We’ll use thecustomer table from thesample database:

The following example uses theLIKE operator to find customers whose first names contain the stringer :

SELECT  first_name,  last_nameFROM  customerWHERE  first_name LIKE '%er%'ORDER BY  first_name;

Output:

first_name  |  last_name-------------+------------- Albert      | Crouse Alberto     | Henning Alexander   | Fennell Amber       | Dixon Bernard     | Colby...

3) Using the LIKE operator a pattern that contains both wildcards

The following example uses theLIKE operator with a pattern that contains both the percent (%) and underscore (_) wildcards:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name LIKE '_her%'ORDER BY  first_name;

Output:

first_name | last_name------------+----------- Cheryl     | Murphy Sherri     | Rhodes Sherry     | Marshall Theresa    | Watson(4 rows)

The pattern_her% matches any strings that satisfy the following conditions:

• The first character can be anything.
• The following characters must be'her'.
• There can be any number (including zero) of characters after'her'.

4) PostgreSQL NOT LIKE examples

The following query uses theNOT LIKE operator to find customers whose first names do not begin withJen:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name NOT LIKE 'Jen%'ORDER BY  first_name;

Output:

first_name  |  last_name-------------+-------------- Aaron       | Selby Adam        | Gooch Adrian      | Clary Agnes       | Bishop...

PostgreSQL extensions of the LIKE operator

PostgreSQLILIKE operator, which is similar to theLIKE operator, but allows forcase-insensitive matching. For example:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name ILIKE 'BAR%';

Output:

first_name | last_name------------+----------- Barbara    | Jones Barry      | Lovelace(2 rows)

In this example, theBAR% pattern matches any string that begins withBAR,Bar,BaR, and so on. If you use theLIKE operator instead, the query will return no row:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name LIKE 'BAR%';

Output:

first_name | last_name------------+-----------(0 rows)

PostgreSQL also provides some operators that mirror the functionality ofLIKE,NOT LIKE,ILIKE,NOT ILIKE, as shown in the following table:

For example, the following statement uses the~~ operator to find a customer whose first names start with the stringDar:

SELECT  first_name,  last_nameFROM  customerWHERE  first_name ~~ 'Dar%'ORDER BY  first_name;

Output:

first_name | last_name------------+----------- Darlene    | Rose Darrell    | Power Darren     | Windham Darryl     | Ashcraft Daryl      | Larue(5 rows)

PostgreSQL LIKE operator with ESCAPE option

Sometimes, the data, that you want to match, contains the wildcard characters% and_. For example:

The rents are now 10% higher than last monthThe new film will have _ in the title

To instruct theLIKE operator to treat the wildcard characters% and_ as regular literal characters, you can use theESCAPE option in theLIKE operator:

stringLIKE pattern ESCAPE escape_character;

Let’screate a simple table for demonstration:

CREATE TABLE t(   message text);INSERT INTO t(message)VALUES('The rents are now 10% higher than last month'),      ('The new film will have _ in the title');SELECT message FROM t;

Note that you’ll learn how tocreate a table andinsert data into it in the upcoming tutorials.

Output:

message---------------------------------------------- The rents are now 10% higher than last month The new film will have _ in the title(2 rows)

The following statement uses theLIKE operator with theESCAPE option to treat the% followed by the number10 as a regular character:

SELECT * FROM tWHERE message LIKE '%10$%%' ESCAPE '$';

Output:

message---------------------------------------------- The rents are now 10% higher than last month(1 row)

In the pattern%10$%%, the first and last% are the wildcard characters whereas the% appears after the escape character$ is a regular character.

Summary

• Use theLIKE operator to match data by patterns.
• Use theNOT LIKE operator to negate theLIKE operator.
• Use the% wildcard to match zero or more characters.
• Use the_ wildcard to match a single character.
• Use theESCAPE option to specify the escape character.
• Use theILIKE operator to match data case-insensitively.

Last updated onJanuary 21, 2024

On this page

• Introduction to PostgreSQL LIKE operator
• PostgreSQL LIKE operator examples
• PostgreSQL extensions of the LIKE operator
• PostgreSQL LIKE operator with ESCAPE option
• Summary