PostgreSQL Like Operator with Multiple Values

In this PostgreSQL tutorial, I will show how to use PostgreSQL Like Operator with Multiple Values.

Also you will understand about wildcard percent symbol (%) which is used with LIKE operator in pattern to match the one or mulitple values. Additonally you will learn how to use the wildcard undeerscore (_) while performing pattern matching.

PostgreSQL Like Operator with Multiple Values

The LIKE operator in PostgreSQL finds the values or information from the tables based on the pattern-matching approach.

Basically, when you use the query containing the PostgreSQL LIKE operator, it matches the value within columns or tables against the pattern which is specified with the LIKE operator using the 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, there are two types of wildcards that 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 with the string or value.

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

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%’ search for the string that starts with the letter ‘U’.

The percent symbol means the string value or charcters after the letter ‘U’ is unknown and only known thing is that the string start with letter ‘U’. So the query finds the string that start with letter ‘U’ and returns the value as True.

Let’s take some examples and understand how the LIKE operator can be used with different pattern-matching approach.

PostgreSQL Like Operator with Multiple Values Substring

You can serach for the substring using the LIKE operator at any position. If you want to find the information from the table and you know only the subpart of that information, then you can search all the information containing that subpart using the LIKE oprator.

The syntax is given below.

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

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

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

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

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

As you can see in the above output, the query returns all the name of country which contains the substring ‘ra’ at different postion in different country name.

Again run the query with multiple values, means also serach for the countries containing 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%');

If you look at the above query, the mulitple LIKE operator is used for mulitple pattern within parenthesis and each LIKE operator is separated by OR operator. The query returns the result containing substring ‘ra’ and ‘S’ in the name of countries as you can see in the output.

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

PostgreSQL Like Operator 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.

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');

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.

PostgreSQL Like Operator 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.

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%');

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.

PostgreSQL Like Operator 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.

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';

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