LIKE Operator

The LIKE operator is used to search the left operand for a character sequence pattern specified by the right operand.

Syntax: 
<left operand> LIKE <character sequence pattern> [ ESCAPE <char> ]
Two wildcard characters can be as specified in the following patterns:
  • % (percent) - matches 0, 1, or multiple characters
  • _ (underscore) - matches a single character
If the character sequence pattern needs to include one of the wildcard characters as one of the characters to match, you can specify an escape character to use by specifying the optional ESCAPE clause after your LIKE pattern as shown in the following example. 
completed LIKE ‘100\%’ ESCAPE ‘\’

Pattern Matching

The pattern matching is not case sensitive for upper or lowercase ASCII characters. For example: 
lastname LIKE ‘long’
searches the lastname columns for values of LONG, lONG, loNG, lonG, long, LoNG, LonG, and so on.
The pattern matching is case sensitive for Unicode characters that are beyond the ASCII range. For example: 
lastname LIKE ‘Ünder’
searches the lastname column for values of ÜNDER, ÜNDEr, ÜNDer, ÜNder, Ünder. It does not search for lastname column values that start with üNDER, üNDEr, and so on.

Pattern matching that is not case sensitive works according to the order of characters in the ASCII table. Uppercase characters sort before lowercase characters. In the ASCII table, A=65 and a=97. Therefore, 'A' sorts before 'a'.

The following table contains examples of character sequence patterns.
Pattern Finds Values That...
'a%' start with 'a' or 'A'
'%a' end with 'a' or 'A'
'a%o' start with 'a' or 'A' and end in 'o' or 'O'
'a_' start with 'a' or 'A' and are two characters long
'_a%' have 'a' or 'A' as the second character
'%at%' contain 'AT', 'At', or 'at'
'a_%_%' start with 'a' or 'A' and are at least 3 characters long
'_____' are exactly 5 characters long
For more information on using LIKE in a WHERE clause, see Operators Used in the WHERE Clause.
Related concepts