In a SELECT statement, the keyword WHERE may be used to set forth conditions which restrict what records are returned. Searching for and retrieving records where a field's value may only partially be known can be done with the LIKE operator.
Pattern matching with LIKE makes use of two special characters: the underscore (_) and the percent (%).
An underscore is used when only one character is needed to match. Consider the following example which selects Jose and Josh from the table but not Joseph.
SELECT * FROM Clients WHERE FirstName LIKE 'Jos_';
LastName FirstName Address City State ZipCode ---------------------------------------------------------------------- Chavez Jose 14 Quailbush Dr Jasper TN 37347 Smith Josh 52 Harding Ln Franklin MA 02038
The WHERE/LIKE condition matches records that have a FirstName column starting with Jos followed by one more character. In this case, Jose and Josh are retrieved, but not Joseph.
The percent sign is used to match zero or more characters, as illustrated by the next example.
SELECT * FROM Clients WHERE FirstName LIKE 'Jos%';
LastName FirstName Address City State ZipCode ---------------------------------------------------------------------- Chavez Jose 14 Quailbush Dr Jasper TN 37347 Adams Joseph 214 Main St Seattle WA 98102 Smith Josh 52 Harding Ln Franklin MA 02038
The WHERE/LIKE condition matches records that have a value in the FirstName column that starts with Jos but is either followed by zero or more characters. In this case Jose, Joseph and Josh are all returned.
Remember, the percent character matches zero or more characters, so a search query for Jose% would return both Jose and Joseph.
Matching characters are not restricted to the end of character strings. Consider the following examples:
SELECT * FROM Clients where Address LIKE '% Main %';
LastName FirstName Address City State ZipCode ---------------------------------------------------------------------- Adams Joseph 214 Main St Seattle WA 98102 Smith Aaron 9112 Main St San Diego CA 92119
SELECT * FROM Clients where Address LIKE '% St';
LastName FirstName Address City State ZipCode ---------------------------------------------------------------------- Adams Gwendolyn 205 W Third St Brownville AL 35020 Smith Harold 321 Elm St Portsmouth RI 02871 Adams Joseph 214 Main St Seattle WA 98102 Copland Dorothy 639 Spring St West Sacramento CA 95691 Smith Aaron 9112 Main St San Diego CA 92119
SELECT * FROM Clients where Address LIKE '_ _ %';
LastName FirstName Address City State ZipCode ---------------------------------------------------------------------- Chavez Jose 14 Quailbush Dr Jasper TN 37347 Johnson Diane 82 Richardson Ave Fresno CA 93702 Smith Josh 52 Harding Ln Franklin MA 02038
Since pattern matching characters can be used anyplace in the WHERE criteria, any substring found within the record's field can be selected.
