SQL Wild cards
Today I will touch on the wild card functionality in SQL. Many times you will be searching for records in a database table by some type of name. Instead of looking specifically for an exact match on a name you can specify a wildcard search in your where statement by using a like clause.
Lets take the following table, classinfo into consideration:
| ID | Name | Address | Class |
|---|---|---|---|
| 1 | Jane | 123 Beech street | Math |
| 2 | John | 1234 Smith street | Math |
| 3 | Jasmin | 4432 Etcher Rd | Science |
| 4 | Chase | 9332 Green St | Music |
Lets suppose we want to get every record that is enrolled in a course whose first letter begins with “M”.
SELECT * FROM CLASSINFO WHERE CLASS LIKE 'M%'
If we want to match on an entire word we use a double percent such as
SELECT * FROM CLASSINFO WHERE CLASS LIKE '%MUSIC%'
We can also match on multiple characters the following, here we will return records that start with M or S.
SELECT * FROM CLASSINFO WHERE CLASS LIKE '[MS]%'
Finally we can also skip characters if we are only interested in the third character for instance in the address field. We can use the _ character. The following will return records whos 3rd character in address is 3.
SELECT * FROM CLASSINFO WHERE ADDRESS LIKE '__3%'