PostgreSQL Like Operator

In this PostgreSQL tutorial, I will show how to use like operator in PostgreSQL.

Additionally, you will learn about the wildcard percent symbol (%), which is used with the LIKE operator in a pattern to match one or multiple values. Additionally, you will learn how to use the wildcard underscore (_) while performing pattern matching.

PostgreSQL Like Operator

The LIKE operator in PostgreSQL searches for values or information in tables using a pattern-matching approach.

When you use a query containing the PostgreSQL LIKE operator, it matches the value within columns or tables against a pattern specified with the LIKE operator using wildcards.

The wildcard is a character that acts as a substitute within a string for one or more characters. This wildcard can be used with the LIKE operator. In general, two types of wildcards are used with the LIKE operator.

• (%): The percent symbol or sign is used for matching one or multiple characters
• (_): The underscore symbol or sign is used for matching only one or a single character.

You can use the above symbols together with the LIKE operator.

The syntax is given below.

string_or_value LIKE pattern

• string_or_value: This can be the text or string value, or it can be the name of the column.
• LIKE: It is the LIKE operator itself.
• Pattern: This is the pattern that you want to match with the string_or_value.

The LIKE operator returns the true value if the pattern matches the string or value.

Let’s take a simple example and understand how the Postgres LIKE operator works.

SELECT 'United States of America' LIKE 'U%';

In the above query, the value is ‘United States of America’, and the pattern ‘U%’ with the LIKE operator returns the result as True because the pattern ‘U%’ searches for the string that starts with the letter ‘U’.

The percent symbol means that the string value or characters after the letter ‘U’ are unknown, and the only known information is that the string starts with the letter ‘U’. So the query finds the string that begins with the letter ‘U’ and returns the value as True.

Let’s examine some examples to understand how the LIKE operator can be used with various pattern-matching approaches.

Example 1: Like Operator with Substring

You can search for the substring using the LIKE operator at any position. Suppose you want to find information from the table, and you know only a subpart of that information. In that case, you can search for all information containing that subpart using the LIKE operator.

Syntax

The syntax is given below.

SELECT column_name1, column_name2,... FROM table_name
WHERE column_name LIKE '%substring%;

In the above syntax, within the WHERE clause, the (LIKE ‘%substring%) searches for the substring within column_name whose starting and ending value is unknown and returns all the values containing that substring.

Let’s see with an example. Suppose you have a table called ‘customers_data’ with columns ‘customer_id’, ‘name’, ‘age’, ‘country’, and ‘phone’, as shown below.

Now you want the name of all the countries that contain the substring ‘ra’ at any position within the name of the country. Use the below query.

SELECT country FROM  customers_data
WHERE country LIKE '%ra%';

After executing the above query, I got the expected output as shown in the screenshot below.

As you can see in the above output, the query returns all the name of countries that contains the substring ‘ra’ in different positions in different country names.

Again, run the query with multiple values, which means also search for the countries containing the substring ‘S’ in the country name. For that, use the below query.

SELECT country FROM  customers_data
WHERE (country LIKE '%ra%'
    OR country LIKE '%S%');

After executing the above query, I got the expected output as shown in the screenshot below.

If you look at the above query, the multiple LIKE operator is used for multiple patterns within parentheses, and the OR operator separates each LIKE operator. The query returns the result containing the substrings ‘ra’ and ‘S’ in the names of countries, as you can see in the output.

This is how you can search for multiple values using the PostgreSQL LIKE operator.

Example 2: Ends with Multiple Values

Suppose you want to find the strings or numbers but you only know the end part of the that strings or numbers, then you can use the PostgreSQL LIKE operator to find all the string or number ending with specifid character or number.

Syntax

The syntax is given below.

SELECT column_name1, column_name2,... FROM table_name
WHERE column_name LIKE '%end_part;

In the above syntax, within WHERE clause, the (LIKE ‘%end_part), searches for stirngs/numbers within column_name whose starting part is unknown and ending part is known, and returns all the value (strings or numbers) containing that specific end part.

Let’s find all the customer whose country name ends with letter ‘A’ and ‘l’ using the below query.

SELECT name, country FROM  customers_data
WHERE (country LIKE '%A'
    OR country LIKE '%l');

After executing the above query, I got the expected output as shown in the screenshot below.

The above query returns all the customer whose country name end with letter ‘A’ and ‘l’ that you can see in the output. Look at the query (country LIKE ‘%A’ OR country LIKE ‘%l’) which contains a mulitple LIKE operator for multiple values, in the pattern LIKE ‘%A’ and LIKE ‘%l’.

The percent symbol before the letters ‘%A’ and ‘%l’ tells that returns all the string which has the specific letters ‘A’ and ‘l’ at the end of string.

Example 3: Starts with Multiple Values

Again you want to find the strings or numbers but you only know the starting part of the that strings or numbers, then you can use the PostgreSQL LIKE operator to find all the string or number starting with specifid character or number.

Syntax

The syntax is given below.

SELECT column_name1, column_name2,... FROM table_name
WHERE column_name LIKE 'starting_part%;

In the above syntax, within WHERE clause, the ( LIKE ‘starting_part%), searches for stirngs/numbers within column_name whose starting part is known and ending part is unknown, and returns all the value (strings or numbers) containing that specific starting part.

Let’s take an example and find all the customer whose country name starts with letter ‘A’ and ‘C’ using the below query.

SELECT name, country FROM  customers_data
WHERE (country LIKE 'A%'
    OR country LIKE 'C%');

After executing the above query, I got the expected output as shown in the screenshot below.

The above query returns all the customer whose country name start with letter ‘A’ and ‘C’ that you can see in the output. Look at the query (country LIKE ‘A%’ OR country LIKE ‘C%’) which contains a mulitple LIKE operator for multiple values, in the pattern LIKE ‘A%’ and LIKE ‘C%’.

The percent symbol after the letters ‘A%’ and ‘C%’ tells that returns all the string which has the specific letters ‘A’ and ‘C’ at the start of string.

Example 4: With Multiple Values for Specific Position

In PostgreSQL, you can use the underscore symbol (_) to match the value for specific position in strings or numbers.

Basically, when you don’t know a specific character of the strings, and you want to find the that strings with only knowing the some character, then in place of the unknown character you can use the underscore which represent the single character with LIKE operator.

Syntax

The syntax is given below.

SELECT column_name1, column_name2,... FROM table_name
WHERE column_name LIKE '_stringPart;

In the above syntax, within WHERE clause, the (LIKE ‘_stringPart), searches for stirngs/numbers within column_name whose starting single character or number is unknown, and returns all the value (strings or numbers) containing that stringPart only.

Let’s take a simple example, suppose in the word USA, you only know the part ‘SA’ and you want to find the country name that first character is unknow and other parts are known as ‘SA’. So you can use the below query.

SELECT name, country FROM  customers_data
WHERE country LIKE '_SA';

After executing the above query, I got the expected output as shown in the screenshot below.

The output of the above query, shows all the customer who belong to country that end with letters ‘SA’ and the first character of that country was unknow. If you want put the underscore for specific unknown character at different position within string, then you can use it.

You can also use the multiple LIKE operator for matching the multiple values.

Conclusion

In this PostgreSQL, you learnt how to use the PostgreSQL LIKE operator to peform the pattern matcing with single or multiple values. Additionally, you covered “what is wildcards and its type with examples?“.

You may also read:

• ILIKE Case Insensitive in PostgreSQL
• How to Escape Single Quote in PostgreSQL
• How to use Postgresql group_concat