Pattern matching

Every pattern defines a set of strings. The LIKE expression returns true if the string is contained in the set of strings that are represented by pattern. The NOT LIKE expression returns false if LIKE returns true, and vice versa. An equivalent expression is NOT (string LIKE pattern).

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any string of zero or more characters.

For example:
  • 'abc' LIKE 'abc' true
  • 'abc' LIKE 'a%' true
  • 'abc' LIKE '_b_' true
  • 'abc' LIKE 'c' false

LIKE pattern matches always include the entire string. To match a pattern anywhere within a string, the pattern must therefore start and end with a percent sign.

To match a literal underscore or percent sign without matching other characters, you must precede the respective character in pattern with the escape character. The default escape character is the backslash, but you can choose a different character by using the ESCAPE clause. To match the escape character itself, enter two escape characters.

To escape the % character, use a backslash, for example:
   SELECT * FROM table WHERE col LIKE '%90\%%'
If you cannot use the backslash character, then designate another ASCII character as the escape character, for example: 
   SELECT * FROM table WHERE col LIKE '%90#%%' escape '#'

It is also possible to select no escape character by entering ESCAPE '' (empty single quotation marks). In this case, there is no way to turn off the special meaning of underscore and percent signs in the pattern.

IBM® Netezza® SQL does not support ILIKE (not case-sensitive search) SQL operators. However, you can always use UPPER() or LOWER() to do searches that are not case-sensitive. For example: 
WHERE UPPER(first_name) LIKE 'PAT%'