Searches for strings that match a pattern.
column-name [NOT] LIKE match-string [ESCAPE escape-character]
Notes: String comparisons in Access or a default installation of SQL Server are case-insensitive. If the column specified by column-name contains a Null, the result is undefined. Comparison of two empty strings or an empty string with the special asterisk (*) character (% character in SQL Server) evaluates to True.
You provide a text string as a match-string value that defines what characters can exist in which positions for the comparison to be true. Access and SQL Server understand a number of wildcard characters (shown in Table-2) that you can use to define positions that can contain any single character, zero or more characters, or any single number.Table-2 Wildcard Characters for String Comparisons
|Desktop Database||Project File||Meaning|
|?||-||Any single character|
|*||%||Zero or more characters (used to define leading, trailing, or embedded strings that don't have to match any of the pattern characters)|
|#||[0-9]||Any single number|
You can also specify in the match string that any particular position in the text or memo field can contain only characters from a list that you provide. To define a list of comparison characters for a particular position, enclose the list in brackets ([ ]). You can specify a range of characters within a list by entering the low-value character, a hyphen, and the high-value character, as in [A-Z] or [3-7]. If you want to test a position for any characters except those in a list, start the list with an exclamation point (!) in a desktop database or a caret symbol (^) in a project file.
If you want to test for one of the special characters *, ?, #, and [, (and _ or % in a project file), you must enclose the character in brackets. Alternatively, in a project file, you can specify an ESCAPE clause. When you place the escape character in the match string, the database ignores the character and uses the following character as a literal comparison value. Therefore, you can include the escape character immediately preceding one of the special characters to use the special character as a literal comparison instead of a pattern character. Desktop databases do not support the ESCAPE clause.
In a desktop database, to determine whether a contact's LastName is at least four characters long and begins with Smi, enter the following:
tblContacts.LastName LIKE "Smi?*"
In a project file, write the previous test as follows:
tblContacts.LastName LIKE 'Smi_%'
In a desktop database, to test whether PostalCode is a valid Canadian postal code, enter the following:
PostalCode LIKE "[A-Z]#[A-Z] #[A-Z]#"
In a project file, to test whether a character column named Discount ends in 5%, enter the following:
Discount LIKE '%5$%' ESCAPE '$'
In this tutorial:
- Understanding SQL
- SQL SELECT Queries
- BETWEEN Predicate
- Comparison Predicate
- EXISTS Predicate
- FROM Clause
- GROUP BY Clause
- HAVING Clause
- IN Clause
- IN Predicate
- LIKE Predicate
- NULL Predicate
- PARAMETERS Declaration
- Quantified Predicate
- Search Condition
- SELECT Statement
- TRANSFORM Statement
- UNION Query Operator
- WHERE Clause
- SQL Action Queries
- INSERT Statement (Append Query)
- SELECT . . . INTO Statement (Make-Table Query)
- UPDATE Statement