LIKE Operator

The LIKE operator uses wildcard characters to match patterns in the data.
If the column specified contains a NULL, then the result is undefined

Character Wildcards

[A-Z]#[A-C]
Example - doc_title LIKE ('&EAFE%')
If you want to include the underscore character as a character to include in your filter you must use the Escape clause. LIKE "%^_%' ESCAPE '^'" is equivalent to LIKE "%_%"
Search conditions can contain either numbers or literal characters. The "%"and "_" can be used in any combination with literal characters. If you need to match the actual characters "%" or "_" then use the Escape option and prefix them with a backslash (\)
Literal characters or strings are in single quotation marks

This works in SQL Server but not in Access since the % is interpreted differently

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE 'a%'  

This works in SQL Server but not in Access

SELECT     DISTINCT   Country 
FROM       CUSTOMERS 
WHERE      Country LIKE '[A-F]%'  

--% in SQL Server means any string of zero or more characters
--% in Access is meant to mean zero or more characters, but doesn't work

_

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE 'a_'  

*

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE 'a*'  

#

This works in Access but not in SQL Server

SELECT     * 
FROM       CUSTOMERS C 
WHERE      NOT C.Address LIKE '*[0-9]*'  
--WHERE    NOT C.Address LIKE '*#*'  

SELECT     * 
FROM       CUSTOMERS C 
WHERE      NOT C.Address LIKE '%[0-9]%'  

?

This works in Access but not in SQL Server

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE '?a*'  

%

This works in SQL Server but not in Access since the % is interpreted differently

SELECT     DISTINCT   Country 
FROM       CUSTOMERS 
WHERE      Country NOT LIKE '[A-F]%'  

Examples

WHERE      CustomerName LIKE 'D*' - all customers that start with the letter D  
WHERE      CustomerName LIKE ''*H'  
WHERE      CustomerName LIKE '[A-C]' - all customers that start with the letter A or the letter B  
WHERE      CustomerName LIKE '[!A-C]' - all customers that don't start with the letter A or the letter B 
WHERE      CustomerName LIKE '*ar*' - all customers that contain the two characters "ar".  
WHERE      CustomerName LIKE 'S?e' - all customers that  
WHERE      CustomerName LIKE '1#' - single placeholder  
WHERE      CustomerName LIKE 'a[!f-h]#'  

--This works in SQL Server but not in Access since the % is interpreted differently

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE 'a%'  

SELECT     DISTINCT Country 
FROM       CUSTOMERS 
WHERE      Country LIKE '[A-F]%'  

--% in SQL Server means any string of zero or more characters
--% in Access is meant to mean zero or more characters, but doesn't work

=--this works in SQL Server but not in Access

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE 'a_'  

=--_ in SQL Server means any single character
--_ in Access is meant to mean a single character, but doesn't work

=--this works in Access but not in SQL Server

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE 'a*'  

--* in SQL Server does not mean anything
--* in Access means zero or more characters

=--this works in Access but not in SQL Server

SELECT     * 
FROM       CUSTOMERS C 
WHERE      NOT C.Address LIKE '*[0-9]*'  
WHERE      NOT C.Address LIKE '*#*'  

--# in Access means a single digit character 0-9

SELECT     * 
FROM       CUSTOMERS C 
WHERE      NOT C.Address LIKE '%[0-9]%'  

--# in SQL Server does not mean anything - you can use % and [0-9] to represent the same thing

=--this works in Access but not in SQL Server

SELECT     * 
FROM       CUSTOMERS C 
WHERE      C.companyname LIKE '?a*'  

--? in SQL Server does not mean anything

--This works in SQL Server but not in Access since the % is interpreted differently

SELECT     DISTINCT Country 
FROM       CUSTOMERS 
WHERE      Country NOT LIKE '[A-F]%'  

--% in SQL Server means any string of zero or more characters
--% in Access is meant to mean zero or more characters, but doesn't work