Wildcards
A wildcard is a character that can be used to substitute for another character or a set of characters.
For example, you can use a wildcard to query for any names that begin with D.
Wildcards are useful in SQL when you want to query a relatively large database and it is either impossible or difficult to write a WHERE clause.
Wildcard characters are used with the SQL operator LIKE
Two wildcard operators we can use are:
- the % sign is used to denote a set of characters
- the _ sign is used to denote a single character
Please note: For wildcards Microsoft Access uses:
- * instead of %
- ? instead of _
The following table has examples on how we could use wildcards:
| Example | Description |
| WHERE surname LIKE ‘McN%’ | Used to find any values in the surname field that start with ‘McN’ |
| WHERE surname LIKE ‘%land’ | Used to find any values in the surname field that end with ‘land’ |
| WHERE surname LIKE ‘%Mc%’ | Used to find any values that have ‘Mc’ anywhere in the surname field |
| WHERE surname LIKE ‘_o%’ | Used to find any values in the surname field that have ‘o’ as the second character |
| WHERE surname LIKE ‘M%e’ | Used to find any values in the surname field that start with ‘M’ and end with ‘e’ |
| Example | WHERE surname LIKE ‘McN%’ |
|---|---|
| Description | Used to find any values in the surname field that start with ‘McN’ |
| Example | WHERE surname LIKE ‘%land’ |
|---|---|
| Description | Used to find any values in the surname field that end with ‘land’ |
| Example | WHERE surname LIKE ‘%Mc%’ |
|---|---|
| Description | Used to find any values that have ‘Mc’ anywhere in the surname field |
| Example | WHERE surname LIKE ‘_o%’ |
|---|---|
| Description | Used to find any values in the surname field that have ‘o’ as the second character |
| Example | WHERE surname LIKE ‘M%e’ |
|---|---|
| Description | Used to find any values in the surname field that start with ‘M’ and end with ‘e’ |
Example
The following example will return all pupils who have more than 50 merit points and have a surname beginning with ‘M’. This example makes use of a wildcard.
SELECT firstname, surname, pastoralCareTeacher, meritPointsFROM PupilWHERE meritPoints > 50 AND surname LIKE 'M%';