LIKE predicate (simple pattern comparison)

A LIKE predicate determines whether an alphanumeric or a national value matches a specified pattern. A pattern is a string that, in addition to normal characters, can also include placeholders and escape characters.

A placeholder represents either one character or else any number of characters. A placeholder can also be used as a normal character in a pattern if its special meaning is canceled with the escape character. You can define the escape character with the ESCAPE clause.

---

operand [NOT] LIKE pattern [ESCAPE character ]

operand ::= expression
pattern::= expression
character::= expression

---

operand

Alphanumeric or national expression representing the operand for the pattern
comparison.

The value of operand must either be atomic or the name of a multiple column. If the operand is a multiple column, the entry for the column cannot be an external reference (i.e. the column of a superordinate query expression).

pattern

Alphanumeric or national expression to which the value from operand is to be matched. pattern can include the following:

• • normal characters (i.e. all except placeholders and escape characters)

  • Placeholder

    Placeholder	Meaning
    _ (underscore) %	one arbitrary character arbitrary (possibly empty) character string

  • escape characters (each followed by a placeholder or another escape character)

Blanks in pattern, even at the beginning or end, are part of the pattern.

ESCAPE clause

You use the ESCAPE clause to define an escape character. If you place an escape character in front of a placeholder, the placeholder loses its function as a placeholder and is interpreted instead as a normal character. You can also use the escape character to cancel the special meaning of the escape character and use it as a normal character.

character

Alphanumeric or national expression whose value has a length of 1. In this comparison, character acts as an escape character.

ESCAPE omitted:

No escape character is defined.

The data types of operand, pattern and character must be comparable, i.e. they all have either one of the data types CHAR and VARCHAR or all have one of the data types NCHAR and NVARCHAR, see also the section "Compatibility between data types".

Result

operand is an atomic value:

Unknown if the value of operand, pattern or character is the NULL value, otherwise

Without NOT:

True if the placeholders for characters and strings in pattern can be replaced by characters and strings, respectively, so that the result is equal to the value of
operand, and has the same length.

False in all other cases.

With NOT:

True if the placeholders for characters and strings in pattern cannot be replaced by characters and strings, respectively, so that the result is equal to the value of
operand, and has the same length.

False in all other cases.

operand is a multiple column:

The pattern comparison is performed for every occurrence in operand.
The individual results are combined with OR.

Examples

Select all the contact people from the CONTACTS table whose first name starts with Ro:

SELECT fname, lname FROM contacts WHERE fname LIKE 'Ro%'
   fname      lname
   Roland     Loetzerich
   Robert     Heinlein

The following statement selects all the rows from table TAB whose column COL starts with the underscore character and ends with at least one space:

SELECT * FROM tab WHERE col LIKE '@_% ' ESCAPE '@'

The following predicate returns true for all three-character values for TITLE whose first character is “M” and whose third character is “.”, i.e. for titles such as “Mr.” or “Ms.”. “_” is a placeholder which stands for any single character. Since the data type for the Title column is TITLE CHAR(20), the string must be padded with blanks to a length of exactly 20 characters.

title LIKE 'M_. ’

The escape character “!” cancels the placeholder “%” with the result that the comparison only returns true for 'Travel expenses%Discount'.

service_text LIKE 'Travel expenses!%Discount ' ESCAPE '!'