LIKE Operator

Checks if a string value matches a given pattern.

Syntax

expression [NOT] LIKE expression

The LIKE operator has these parts:

Part	Description
expression	The value of interest and a pattern.

Remarks

Use the LIKE operator to check if a string value matches a given pattern. If a value matches a pattern, the operator returns True; otherwise, it returns False.

In a pattern, use the underscore character (_) to match any single character. Use the percent sign (%) to match any number of characters including zero. Use the backslash character and the underscore character (\_) to match the underscore character, use the backslash character and the percent sign (\%) to match the percent sign, use two backslash characters (\\) to match the backslash character. Use any other character, like a letter or a digit, to match itself.

The LIKE operator is case-sensitive. To match values without case, convert both the string and the pattern to either upper or lower case with UCase or LCase.

If either the checked value or the pattern is NULL, the LIKE operator also returns NULL.

Examples

The following table shows sample expressions using the LIKE operator and the value returned by each expression:

String Expression	Returns
abbc LIKE a%	TRUE
abbc LIKE _c	FALSE
abbc LIKE %c	TRUE
abbc LIKE %bb	FALSE
a_bc LIKE %	TRUE
a_bc LIKE a_bc	TRUE (because _ matches any character)
a_bc LIKE a\_bc	TRUE(because \_ matches _)
a_bc LIKE a__c	TRUE
ab\c LIKE %\%	FALSE
ab\c LIKE %\\%	TRUE

This example uses the LIKE operator to select all products the name of which starts with "C":

SELECT * FROM [Products] WHERE [Product Name] LIKE "C%";

This example uses the LIKE operator to select all products the name of which ends with "n":

SELECT * FROM [Products] WHERE [Product Name] LIKE "%n";

This example uses the LIKE operator to select all customers whose contact title includes the word "sales" (ignoring case):

SELECT * FROM [Customers] WHERE LCase([Contact Title]) LIKE "%sales%";

This example uses the LIKE operator to select all customers whose phone number contains a non-empty area code in parentheses:

SELECT * FROM [Customers] WHERE [Phone] LIKE "%(_%)%";

This example uses the LIKE operator and the PARAMETERS declaration to locate all customers whose company name matches a pattern supplied as a parameter:

PARAMETERS [Pattern] TEXT;

SELECT * FROM [Customers] WHERE [Company Name] LIKE [Pattern];

Back to Manifold Home Page