Microsoft Access SQL Operators: LIKE

	Microsoft Access SQL Operators: LIKE

Introduction

The LIKE operator uses the wildcards to perform data analysis. To use it, you can use:

To use Filter By Form on a table, a query, or a form, after opening the list, click the combo box under a column and type the LIKE expression
To use a grid, use the Advanced Filter/Sort option from the Advanced button of the Ribbon. Select the column you want to use. In the Criteria box that corresponds to that column, enter the LIKE expression.

To use a LIKE operation in SQL, after the SELECT expression, type LIKE followed by the intended expression. When you apply the filter, all records that abide by the expression would be in the result.

LIKE an Exact String

To find an exact string, type LIKE followed by the string in double-quotes. Here is an example:

SELECT FirstName, LastName, Gender
FROM   Students
WHERE  LastName LIKE "Bidoula";

LIKE a Date/Time Value

If the value you want to find is a date, include it between two # signs. Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM   Students
WHERE  DateOfBirth LIKE #10/16/1991#;

LIKE Multiple Characters

To get a list of strings that include any characters as a substring in a string, use the * symbol. For example, if you do not know how the string starts but you know how it ends, type LIKE "*". Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM   Students
WHERE  LastName LIKE "*son";

If you do not know how the string starts or how it ends but know one or more characters that it includes, surround the substring with * symbols. Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM Students
WHERE LastName LIKE "*et*";

LIKE a Range of Characters

To specify that you want the strings that start with any character from o to r followed by anything, you would use LIKE "[o-r]*". Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM Students
WHERE (((LastName) LIKE "[o-r]*"));

In the same way, as stated already, you can use the * to the left, to the right or both. Also, you can combine the [] placeholder with the other wildcards.

NOT LIKE Excluding a Range of Characters

To negate the filter of a range of characters, you can precede the LIKE operation with the NOT operator. Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM   Students
WHERE  LastName) NOT LIKE "[c-w]*"));

As an alternative, you can start the inside of the [] placeholder with !. An example would be LIKE "[!o-r]*". This would result in all records that do not start with o, p, or r.

LIKE a Specific Character

To use specific characters, use the [] as the placeholder. Inside the square brackets, enter the characters separated by commas. Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM Students
WHERE LastName LIKE "[a, h, o, y]*";

To negate the filter, you can precede the LIKE operation with the NOT operator. An example would be NOT LIKE "[a, h, o, y]*". As an alternative, inside the [] placeholder, start with the ! symbol. An example would be LIKE "[!a, h, o, y]*". In both cases, the list would include records that do not start with a, h, o, or y.

LIKE a Single Character

The * character on a LIKE operation is used to find many characters. If you want to find only one character, use the ? wildcard. For example, if you do not know by what character a string starts but know one or more characters after it, you can use ? followed by a substring. An example would be LIKE "?ane". This would produce such strings as Pane, Bane, or Sane.

If you do not know by what character a string starts, know the second character, but do not remember the rest of the characters, you can combine the ? and the * symbols. Here is an example:

SELECT FirstName, LastName, DateOfBirth, Gender
FROM   Students
WHERE  LastName LIKE "?i*";


Home	Copyright © 2008-2009 FunctionX, Inc.