The SQL primary command LIKE, also known as the LIKE operator, can be used for wildcard comparison against String values to filter specific data. The LIKE operator has three special characters, each with a different usage: the percent sign (%), the underscore (_), and the caret (^).

 

% multi-character wildcard

 

The use of the percent sign (%) in a LIKE clause retrieves matches of zero or more characters. 

 

Example:

If the column TEST contains "ABCDEF" the expression TEST LIKE "%F" returns TRUE, the expression TEST LIKE "%C%" returns TRUE, and the expression TEST LIKE "%G" returns FALSE.

_ single-character wildcard

 

The use of an underscore (_) in a LIKE clause retrieves matches of a single character.

 

Example:

If the column TEST contains "GHIJKLM" the expression TEST LIKE "G_IJKLM" returns TRUE, and the expression TEST LIKE "A_IJKLM" returns FALSE.

 

^ literal character identifier

 

The use of the caret (^) in a LIKE cause permits the use of any of the wildcard characters %, _ or ^. If the ^ character is placed in front of either of the other two wildcard characters, the characters following are treated as normal literals.

 

Example:

If the column TEST contains "10% of the data" the expression TEST LIKE "10^% of the data" returns TRUE, the expression TEST LIKE "10%data" returns TRUE, and the expression TEST LIKE "10data" returns FALSE.

 

Important: The Microsoft Jet Engine for Microsoft Access uses an asterisk (*) as the wildcard character instead of the percent sign (%). Replace the percent sign (%) wildcard with an asterisk (*) when using Microsoft Access, or when using other DAO-compliant applications.