LIKE Operator

Checks if a string value matches a given pattern.

 

<value> LIKE <pattern>

 

The LIKE operator has these parts:

 

 

 

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.

 

Special characters used in patterns:

_   Underscore character - matches any single character.

 

%  Percent sign - matches any number or characters including no characters.

 

\_  Backslash character with underscore character - matches the underscore character.

 

\%  Backslash character with percent sign - matches the percent sign.

 

\\   Backslash character with backslash character - matches 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 StringToUpperCase or StringToLowerCase functions.

 

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

 

Sample Expressions

Sample expressions using the LIKE operator and the value returned by each expression.  

 

abbc LIKE a%  returns TRUE

 

abbc LIKE _c  returns FALSE

 

abbc LIKE %c  returns TRUE

 

abbc LIKE %bb  returns FALSE

 

a_bc LIKE %  returns TRUE

 

a_bc LIKE a_bc  returns TRUE (because _ matches any character)

 

a_bc LIKE a\_bc  returns TRUE  (because \_ matches _)

 

a_bc LIKE a__c  returns TRUE

 

ab\c LIKE %\%  returns FALSE

 

ab\c LIKE %\\%  returns TRUE

 

Examples

 Use the LIKE operator to select all products the name of which starts with C:

 

SELECT * FROM [Products]

WHERE [Name] LIKE 'C%';

 

Use the LIKE operator to select all products the name of which ends with r:

 

SELECT * FROM [Products]

WHERE [Name] LIKE '%r';

 

Use the LIKE operator to select all products the name of which includes the word Anton (ignoring case):

 

SELECT * FROM [Products]

WHERE StringToLowerCase([Name]) LIKE '%anton%';

 

Using a table that contains the names of provinces in Mexico:

 

SELECT [NAME] FROM [Mexico Table]

  WHERE [NAME] LIKE 'Dur%';

 

Returns Durango.

 

SELECT [NAME] FROM [Mexico Table]

  WHERE [NAME] LIKE '%an%';

 

Returns Guanajuato, Michoacan de Ocampo, Yucatan, Quintana Roo, Durango and San Luis Potosi.