Previous Page | Next Page

The SQL Procedure

LIKE condition

Tests for a matching pattern.
sql-expression <NOT> LIKE sql-expression <ESCAPE character-expression>



is described in sql-expression.


is an sql-expression that evaluates to a single character. The operands of character-expression must be character or string literals.

Note:   If you use an ESCAPE clause, then the pattern-matching specification must be a quoted string or quoted concatenated string; it cannot contain column names.  [cautionend]


The LIKE condition selects rows by comparing character strings with a pattern-matching specification. It resolves to true and displays the matched strings if the left operand matches the pattern specified by the right operand.

The ESCAPE clause is used to search for literal instances of the percent (%) and underscore (_) characters, which are usually used for pattern matching.

Patterns for Searching

Patterns consist of three classes of characters:

underscore (_)

matches any single character.

percent sign (%)

matches any sequence of zero or more characters.

any other character

matches that character.

These patterns can appear before, after, or on both sides of characters that you want to match. The LIKE condition is case-sensitive.

The following list uses these values: Smith , Smooth , Smothers , Smart , and Smuggle .


matches Smith , Smooth , Smothers , Smart , Smuggle .


matches Smith , Smooth .


matches Smuggle .


matches a three-letter word, so it has no matches here.


matches Smooth , Smothers .


matches Smith , Smooth .


matches the single, uppercase character Z only, so it has no matches here.

Searching for Literal % and _

Because the % and _ characters have special meaning in the context of the LIKE condition, you must use the ESCAPE clause to search for these character literals in the input character string.

These examples use the values app , a_% , a__ , bbaa1 , and ba_1 .

Searching for Mixed-Case Strings

To search for mixed-case strings, use the UPCASE function to make all the names uppercase before entering the LIKE condition:

   upcase(name) like 'SM%';

Note:   When you are using the % character, be aware of the effect of trailing blanks. You might have to use the TRIM function to remove trailing blanks in order to match values.  [cautionend]

Previous Page | Next Page | Top of Page