LIKE Predicate
Use LIKE in a WHERE clause to search for a specified pattern in a column.
LIKE performs pattern matching for the character and Unicode data types.
The LIKE predicate has the following syntax:
expression [NOT] LIKE pattern [WITH CASE | WITHOUT CASE]
[WITH DIACRITICAL | WITHOUT DIACRITICAL]]
[ESCAPE escape_character]
where
expression
Is a column name or an expression containing string functions
pattern
Specifies the pattern to be matched. The pattern is typically a string literal but can be an arbitrary string expression.
The pattern-matching (wild card) characters are as follows:
% (percent sign)
Denotes 0 or more characters
_ (underscore)
Denotes a single character
\[ set \]
Denotes a single character from the set
\|
Denotes the alternation operator
WITH CASE | WITHOUT CASE
Indicates whether to match the case of the pattern. This option can be used before, after, or instead of the ESCAPE clause.
Default: WITH CASE
WITH DIACRITICAL | WITHOUT DIACRITICAL
Indicates whether to ignore diacritical marks. For example, Latin little "e" will match Latin little "e" with acute if the WITHOUT DIACRITICAL is specified.
Default: WITH DIACRITICAL
ESCAPE escape_character
Specifies an escape character, which suppresses any special meaning for the character following it, allowing the character to be entered literally. The following characters can be escaped:
• The pattern-matching characters % and _.
• The escape character itself. To enter the escape character literally, type it twice.
• Square brackets [ ]. Within escaped square brackets ( [ and ] ), a series of individual characters or a range of characters separated by a dash (-) can be specified.
• The | character, which can be used to specify alternate patterns to match.
Note: The dash (-) cannot be escaped, so to include a dash as a literal character in a range pattern it must be the first character in a range:
SELECT * FROM bob WHERE FIRST LIKE '\[-.,;:&@("/)+\]' ESCAPE '\'
LIKE does not ignore the trailing blanks present with a char or nchar data type. If you are matching a char value (that is padded with blanks when it is inserted) or if the value has trailing blanks that were entered by the user, include these trailing blanks in your pattern. For example, if searching a char(10) column for rows that contain the name harold, specify the trailing blanks in the pattern to be matched:
name like 'harold '
Four blanks are added to the pattern after the name to include the trailing blanks.
Because blanks are not significant when performing comparisons of c data types, the LIKE predicate returns a correct result whether or not trailing blanks are included in the pattern.
LIKE Examples
The following examples illustrate some uses of the pattern-matching capabilities of the LIKE predicate.
To match any string starting with a:
name LIKE 'a%'
To match any string starting with A through Z:
name LIKE '\[A-Z\]%' ESCAPE '\'
To match any two characters followed by 25%:
name LIKE '__25\%' ESCAPE '\'
To match a string starting with a backslash. Because there is no ESCAPE clause, the backslash is taken literally:
name LIKE '\%'
To match a string starting with a backslash and ending with a percent:
name LIKE '\\%\%' ESCAPE '\'
To match any string starting with 0 through 4, followed by an uppercase letter, then a [, any two characters and a final ]:
name LIKE '\[01234\]\[A-Z\][__]' ESCAPE '\'
To detect names that start with S and end with h, disregarding any leading or trailing spaces:
SQUEEZE(name) LIKE 'S%h'
To detect a single quote, repeat the quote:
name LIKE ''''
To search for multiple patterns, use escaped | as a delimiter. For example, the following will match if string_1 contains ABC, 123, or xyz:
string_1 LIKE '%ABC%@|%123%@|%xyz%' ESCAPE '@'