LIKE or MATCHES Condition

The LIKE or MATCHES condition is satisfied if either of the following is true:

• The value of the column that precedes the LIKE or MATCHES keyword matches the pattern that the quoted string specifies. You can use wildcard characters in the string.
• The value of the column that precedes the LIKE or MATCHES keyword matches the pattern that is specified by the column that follows the LIKE or MATCHES keyword. The value of the column on the right serves as the matching pattern in the condition.

The following SELECT statement returns all rows in the customer table in which the lname column begins with the literal string 'Baxter'. Because the string is a literal string, the condition is case sensitive.

SELECT * FROM customer WHERE lname LIKE 'Baxter%' ;

The next SELECT statement returns all rows in the customer table in which the value of the lname column matches the value of the fname column:

SELECT * FROM customer WHERE lname LIKE fname;

The following examples use the LIKE condition with a wildcard. The first SELECT statement finds all stock items that are some kind of ball. The second SELECT statement finds all company names that contain a percent ( % ) sign. Backslash ( \ ) is used as the default escape character for the percent ( % ) sign wildcard. The third SELECT statement uses the ESCAPE option with the LIKE condition to retrieve rows from the customer table in which the company column includes a percent ( % ) sign. The z is used as an escape character for the percent ( % ) sign:

SELECT stock_num, manu_code FROM stock 
   WHERE description LIKE '%ball';
SELECT * FROM customer WHERE company LIKE '%\%%';
SELECT * FROM customer WHERE company LIKE '%z%%' ESCAPE 'z';

The following examples use MATCHES with a wildcard in SELECT statements. The first SELECT statement finds all stock items that are some kind of ball. The second SELECT statement finds all company names that contain an asterisk ( * ). The backslash ( \ ) is used as the default escape character for a literal asterisk ( * ) character. The third statement uses the ESCAPE option with the MATCHES condition to retrieve rows from the customer table where the company column includes an asterisk ( * ). The z character is specified as an escape character for the asterisk ( * ) character:

SELECT stock_num, manu_code FROM stock 
   WHERE description MATCHES '*ball';

SELECT * FROM customer WHERE company MATCHES '*\**';

SELECT * FROM customer WHERE company MATCHES '*z**' ESCAPE 'z';