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

CharacterAccessSQL Server
?single character 
*zero or more characters 
_ single character
% zero or more characters
#single digit [0-9] 
[ characters ]single character in a group of characters 
[ ! characters ]single character not in a group of characters 

[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




© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext