LIKE and MATCHES Condition

A LIKE or MATCHES condition tests for matching character strings.

The condition is TRUE, or satisfied, when either of the following tests is TRUE:
  • The value of the column on the left matches the pattern that the quoted string specifies. You can use wildcard characters in the string. NULL values do not satisfy the condition.
  • The value of the column on the left matches the pattern that the column on the right specifies. The value of the column on the right serves as the matching pattern in the condition.

If the quoted string includes literal characters that match any of the wildcard characters that the LIKE or MATCHES operator recognizes, the ESCAPE clause can define an ASCII character that you can include in the quoted string. When the column value on the left is compared to the quoted string, the next character that immediately follows this escape character is interpreted as a literal character, rather than as a wildcard, and the escape character is ignored. The LIKE and MATCHES operators recognize different wildcard characters. For more information about LIKE and MATCHES escape characters, see ESCAPE with LIKE and ESCAPE with MATCHES topics.

You can use the single quotation mark ( ' ) only with the quoted string to match a literal single quotation mark; you cannot use the ESCAPE clause. You can use the single quotation mark character as the escape character in matching any other pattern if you write it as this: ''''.

Important: Columns that you specify in LIKE or MATCHES conditions should be simple character data types, like CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR. You cannot, for example, specify a complex data type, such as a ROW-type column, in a LIKE or MATCHES condition. (A ROW-type column is a column that is declared as a named or unnamed ROW type.) Similarly, the database server cannot evaluate a condition that uses LIKE or MATCHES with a simple or smart large object column, such as a CLOB column; a query that includes this condition fails with error -640.