Power Selecting - Like
(Page 5 of 6 )
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.
Next: Conclusion >>
More Database Articles Articles
More By bluephoenix
