How to use the SQL Like / ALike operator

You can use the SQL Like / ALike operator to find values in a field that match the pattern you specify. The power of Like / ALike is that it alows you to use wildcard characters to find a range of values. For details on the difference between Like and ALike read the Notes


SELECT * FROM Products WHERE Products.ProductName ALike 'Chef%';
sql alike result

The patterns that you can choose from are:

%: any string of any length (including zero length - percent sign wildcard )
_: a single character (underscore wildcard)

You can use multiple wildcards in your Like pattern, e.g. ALike '%Chef%' will also return records with text before 'Chef'.

If you want all records expect the ones with 'Chef' use the Not operator: Not ALike '%Chef%'

Notes
  • In SQL-89 compatibility mode LIKE only works with * and ? patterns but if you change the compatibility to SQL-92, you need to rewrite your queries with % and _ instead. ALIKE allows you to write ANSI compliant patterns regardless of the compatibility level you choose (so in SQL-92 compatibility mode, both are behaving the same).
  • In Access 2010, the setting for SQL Server Syntax compatibility is under: File » Options » Object Designers » Query Design
Warning
From Access 2010 onwards, the sql editor will automatically change the keyword Like to ALike, but does not change the wildcards accordingly, which will make the query no longer work correctly,