Summary: in this tutorial, you will learn how to use Db2 LIKE operator to search for a string that has a certain pattern.
Introduction to Db2 LIKE operator
The Db2 LIKE operator is a logical operator that returns true if a string contains a certain pattern. The pattern may include regular characters and special characters called wildcards.
The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to form the predicate for filtering rows based on a certain pattern.
The following shows the syntax of the LIKE operator:
1 | {column_name | expression} LIKE pattern [ESCAPE escape_character] |
Pattern
The pattern is a string for which to search in the column_name or expression. It may include the percent (%) and underscore (_) wildcard characters.
- The percent wildcard (%) represents any string of zero or more characters.
- The underscore (_) wildcard represents any single character.
For example, s% matches any string starts with s and followed by any number of characters. %SQL% matches any string that contains the word SQL. S_r matches any string that starts with S, followed by any character, and ended with the letter r like Sir, Ser, Str, Sor…
ESCAPE escape_character
Sometimes, you may want to find a pattern that contains the wildcard characters like % or _. In this case, you can use the escape character. The escape character instructs the LIKE operator to treat the wildcard characters as the regular characters.
Db2 LIKE operator examples
We will use the books table from the sample database to demonstrate the LIKE operator.
1) Using Db2 LIKE operator with % wildcard examples
This example uses the LIKE operator to find books that have the string C++ at the end of the title:
1 2 3 4 5 6 7 8 | SELECT title FROM books WHERE title LIKE '%C++' ORDER BY title; |
To get books whose titles contain the string C++, you use the following query:
1 2 3 4 5 6 7 8 | SELECT title FROM books WHERE title LIKE '%C++%' ORDER BY title; |
2) Using LIKE operator with _ wildcard examples
This example uses the LIKE operator to find books whose title starts with a pattern D_t_ e.g., Data, Database:
1 2 3 4 5 6 7 8 | SELECT title FROM books WHERE title LIKE 'D_t_%' ORDER BY title desc; |
Let’s examine the pattern D_t_% in detail.
The matched string must start with the letter D, followed by any single character (_), and then the letter t, and followed by any single character (_), and finally followed by zero or more character (%).
Here is the output of the query:
Db2 NOT LIKE operator
To negate the LIKE operator, you use the NOT operator as follows:
1 | {column_name | expression} NOT LIKE pattern [ESCAPE escape_character] |
This example uses the NOT LIKE operator to find books whose title does not contain the word Programming:
1 2 3 4 5 6 7 8 | SELECT title FROM books WHERE title NOT LIKE '%Programming%' ORDER BY title; |
Here is the result set:
In this tutorial, you have learned how to use the Db2 LIKE operator to query data that has a certain pattern.