Wildcards
Wildcards
A wildcard character is used to replace one or more characters in a string. They are useful in situations where incomplete information is available and it's impossible to write a WHERE clause using =, <, > <= or >=. You don't always know every part of a record. For example you may only remember the beginning of a piece of text or you may want all schools that have the word "Academy" in them.
For this we can use the wildcard operators. This means that you specify the parts that you know.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to perform search operations.
Two different wildcards can be used:
The percentage symbol (%) is used to represent zero, one or multiple characters.
The underscore (_) is used to represent a single character.
The table below are examples of LIKE used with the wildcards:
Example Wildcard operators
Using the online shop database here we will look at some examples.
Wildcard Example 1
You need to display the description and price of all items with SODIMM in their description with the most expensive being displayed first.
Example 1 - Query Design
Fields and calculations: description, price
Tables: Item
Search Criteria: description LIKE '%SODIMM%'
Grouping:
Sort Order: price DESCENDING
Explanation:
This will match any string that has the word SODIMM with any amount of text before or after it.
Example 1 - Query Implementation
Wildcard Example 2
The company wishes to run a promotion - they need the customer details of all customers with a DOB in June. The results should be displayed in chronological order then alphabetical order.
Example 2 - Query Design
Fields and calculations: *
Tables: Customer
Search Criteria: DOB LIKE '%-06-%'
Grouping:
Sort Order: DOB, surname, forename
Explanation:
This will match any DOB with the 6th month. Remember that mySQL dates are in the format yyyy/mm/dd
Example 2 - Query Implementation
Wildcard Example 3
There is a problem with deliveries in a particular area. The company needs to find all customers names and address' who live in the LN12 area.
Example 3 - Query Design
Fields and calculations: forename,surname,address,postcode
Tables: Customer
Search Criteria: postcode LIKE 'LN12%'
Grouping:
Sort Order:
Explanation:
it is important that the postcode BEGINS with LN12 so there is no wildcard operator before LN12