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

All the following examples use LIKE with the Surname column in the Contacts table. Queries are of the form:

SELECT Surname
   FROM Contacts
   WHERE Surname LIKE match-expression;

The first example would be entered as

SELECT Surname
   FROM Contacts
   WHERE Surname LIKE 'Mc%';

Match expression

Description

Returns

'Mc%'

Search for every name that begins with the letters Mc

McEvoy

'%er'

Search for every name that ends with er

Brier, Miller, Weaver, Rayner

'%en%'

Search for every name containing the letters en.

Pettengill, Lencki, Cohen

'_ish'

Search for every four-letter name ending in ish.

Fish

'Br[iy][ae]r'

Search for Brier, Bryer, Briar, or Bryar.

Brier

'[M-Z]owell'

Search for all names ending with owell that begin with a single letter in the range M to Z.

Powell

'M[^c]%'

Search for all names beginning with M' that do not have c as the second letter

Moore, Mulley, Miller, Masalsky

Wildcards require LIKE

Wildcard characters used without LIKE are interpreted as string literals rather than as a pattern: they represent exactly their own values. The following query attempts to find any phone numbers that consist of the four characters 415% only. It does not find phone numbers that start with 415.

SELECT Phone
   FROM Contacts
   WHERE Phone = '415%';

Using LIKE with date and time values

You can use LIKE on DATE, TIME, TIMESTAMP, and TIMESTAMP WITH TIME ZONE fields. However, the LIKE predicate only works on character data. When you use LIKE with date and time values, the values are implicitly CAST to CHAR or VARCHAR using the corresponding option setting for DATE, TIME, TIMESTAMP, and TIMESTAMP WITH TIME ZONE data types to format the value:

Date/time type	CAST to VARCHAR using
DATE	date_format
TIME	time_format
TIMESTAMP	timestamp_format
TIMESTAMP WITH TIME ZONE	timestamp_with_time_zone_format

A consequence of using LIKE when searching for DATE, TIME or TIMESTAMP values is that, since date and time values may contain a variety of date parts, and may be formatted in different ways based on the above option settings, the LIKE pattern has to be written carefully to succeed.

For example, if you insert the value 9:20 and the current date into a TIMESTAMP column named arrival_time, the following clause will evaluate to TRUE if the timestamp_format option formats the time portion of the value using colons to separate hours and minutes:

WHERE arrival_time LIKE '%09:20%'

In contrast to LIKE, search conditions that contain a simple comparison between a string literal and a DATE, TIME, TIMESTAMP, or TIMESTAMP WITH TIME ZONE value use the date/time data type as the comparison domain. In this case, SQL Anywhere first converts the string literal to a TIMESTAMP value and then uses the necessary portion(s) of that value to perform the comparison. SQL Anywhere follows the ISO 8601 standard for converting TIME, DATE, and TIMESTAMP values, with additional extensions.

For example, the clause below will evaluate to TRUE because the constant string value 9:20 is converted to a TIMESTAMP using 9:20 as the time portion and the current date for the date portion:

WHERE arrival_time = '9:20'

Using NOT LIKE

With NOT LIKE, you can use the same wildcard characters that you can use with LIKE. To find all the phone numbers in the Contacts table that do not have 415 as the area code, you can use either of these queries:

SELECT Phone
   FROM Contacts
   WHERE Phone NOT LIKE '415%';

SELECT Phone
   FROM Contacts
   WHERE NOT Phone LIKE '415%';

Pattern matching character strings in the WHERE clause

Examples

Wildcards require LIKE

Using LIKE with date and time values

Using NOT LIKE

Using underscores

See also