DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 LIKE

Db2 LIKE

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:

{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:

SELECT title FROM books WHERE title LIKE '%C++' ORDER BY title;
Db2 LIKE with percent wildcard example 1

To get books whose titles contain the string C++, you use the following query:

SELECT title FROM books WHERE title LIKE '%C++%' ORDER BY title;
Db2 LIKE with percent wildcard example

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:

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 LIKE with underscore wildcard example

Db2 NOT LIKE operator

To negate the LIKE operator, you use the NOT operator as follows:

{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:

SELECT title FROM books WHERE title NOT LIKE '%Programming%' ORDER BY title;

Here is the result set:

Db2 NOT LIKE example

In this tutorial, you have learned how to use the Db2 LIKE operator to query data that has a certain pattern.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 BETWEEN
Next Db2 IN

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.