Hack 50. Use Wildcards in Queries

The Like operator comes in handy when you don't quite remember how to spell a data item.

When you can remember only a partial amount of information, a great way to search through your database records is to use the SQL Like operator. Combining Like with wildcards makes for some powerful queries.

For example, imagine you have a database table filled with customer records. You need to look up someone whose last name starts with De, and that's all you can recall about her name.

Figure 5-41 shows a query that uses the asterisk (*) wildcard to find all customers whose last name starts with De. The asterisk is a placeholder for any number of characters. Therefore, running this query returns all customers with De as the first two characters in their last name.

Let's say instead that you remember the last name starts with D and is four characters long. In this case, the question mark (?) wildcard comes in handy. You use the question mark as a placeholder to represent a single character. Figure 5-42 shows three question marks being used to make up for three spaces after the letter D.

Figure 5-43 shows the result of running the query. All customers with four-character last names that start with D are returned.

Using wildcards lets you really get to your data in creative ways. You might recall the first and last letter of a name, or even just that a name is four characters long, but you don't remember any of the actual characters! In such as case, using a criterion of Like "????" returns all customers with last names that are four characters long, as shown in Figure 5-44.

Figure 5-41. Finding customers with an asterisk wildcard

Figure 5-42. Finding customers using question marks

Figure 5-43. Returning records based on the wildcards

Figure 5-44. Returning all customers with a last name four characters long