Pattern matching character strings in the WHERE clause

Pattern matching is a versatile way of identifying character data. In SQL, the LIKE keyword is used to search for patterns. Pattern matching employs wildcard characters to match different combinations of characters.

The LIKE keyword indicates that the following character string is a matching pattern. LIKE is used with character data.

The syntax for LIKE is:

expression [ NOT ] LIKE match-expression

The expression to be matched is compared to a match-expression that can include these special symbols:

Symbols Meaning
% Matches any string of 0 or more characters
_ Matches any one character
[specifier]

The specifier in the brackets may take the following forms:

  • Range   A range is of the form rangespec1-rangespec2, where rangespec1 indicates the start of a range of characters, the hyphen indicates a range, and rangespec2 indicates the end of a range of characters.

  • Set   A set can include any discrete set of values, in any order. For example, [a2bR].

Note that the range [a-f], and the sets [abcdef] and [fcbdae] return the same set of values.

[^specifier] The caret symbol (^) preceding a specifier indicates non-inclusion. [^a-f] means not in the range a-f; [^a2bR] means not a, 2, b, or R.

You can match the column data to constants, variables, or other columns that contain the wildcard characters displayed in the table. When using constants, you should enclose the match strings and character strings in single quotes.

 Examples
 Wildcards require LIKE
 Using LIKE with date and time values
 Using NOT LIKE
 Using underscores
 See also