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

 

Important: The "I" in ILIKE represents an abbreviated form of the word "insensitive"; therefore, the ILIKE operator is NEVER case-sensitive (even if the database tables are case-sensitive).

 

% multi-character wildcard

 

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

 

Example:

If the column TEST contains 'ABCDEF' the expression TEST ILIKE '%F' returns TRUE, the expression TEST ILIKE '%C%' returns TRUE, and the expression TEST ILIKE '%G' returns TRUE.

 

Likewise, the expression TEST ILIKE 'f%' returns TRUE.

 

 

_ single-character wildcard

 

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

 

Example:

If the column TEST contains 'GHIJKLM' the expression TEST ILIKE 'G_IJKLM' returns TRUE, and the expression TEST ILIKE 'A_IJKLM' returns TRUE.

 

Likewise, the expression TEST ILIKE 'A_ijkLM' returns TRUE.

 

 

^ literal character identifier

 

The use of the caret (^) in a ILIKE 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 ILIKE '10^% of the data' returns TRUE, the expression TEST ILIKE '10%data' returns TRUE, and the expression TEST ILIKE '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.