Wild Card operators in SQL
As Data Analysts, we often need to search and filter data that can be further manipulated for analysis. Wild card operators are very useful for filtering data in SQL. A wildcard character in SQL replaces in a string zero with any number of characters. Usually, these wildcard characters are used with the SQL operator LIKE. The LIKE operator is used with the WHERE clause. Wild card operators workwords similarly to Regular Expressions.
A wildcard character is a substitute character that is used to replace specific other characters or characters in that string. These wildcards are beneficial when we want to quickly search in the database.
Wildcard characters can be either used as a prefix or a suffix. In fact, wildcard operators can be used anywhere (prefix, suffix, in between) in word. There can be more than one wildcard in a word.
There are two common wildcard characters in SQL. The percent symbol (%) signifies zero, one or more number of characters. The underscore represents a single number of characters.
Let us look at the wildcard characters in detail, along with other useful characters.
Wild Card Operators | Function |
Percent sign % | This wildcard matches zero or more characters |
Underscore _ | This wildcard matches only a specific single character |
Caret ^ | This wildcard matches characters which are not present in the bracket after this symbol |
Square brackets [] | This wildcard matches a single character specified within the brackets |
Apostrophe ! | This wildcard list the data which does not begin with the character specified with ! |
Hyphen - | This wildcard will display any single character within the specified range. |
All these wild card operators can also be used in combinations with LIKE Operators.
Let us learn how to use these wild card operators with an example. We are using a simple database for this example.
Patient ID | Patient Name | Age | Sex | State |
1 | John | 45 | Male | New York |
2 | Diana | 33 | Female | Nevada |
3 | Ben | 29 | Male | Atlanta |
4 | Valerie | 39 | Female | Nebraska |
5 | Susan | 40 | Female | New Jersey |
6 | Mary | 43 | Female | Florida |
7 | Greg | 32 | Male | Tennesse |
8 | Cameron | 37 | Male | Minnesota |
9 | Anatasia | 29 | Female | Texas |
10 | Anita | 35 | Female | Georgia |
11 | Tony | 35 | Male | Atlanta |
12 | Gabriella | 39 | Female | New Mexico |
1. Working with the % wildcard operator
SELECT * FROM Patient Name
WHERE State LIKE 'New%';
The above example will return all the patient names from the state starting with " New"
Result for the above query is --
Patient ID | Patient Name | Age | Sex | State |
1 | John | 45 | Male | New York |
5 | Susan | 40 | Female | New Jersey |
12 | Gabriella | 39 | Female | New Mexico |
SELECT * FROM Patient Name
WHERE State LIKE '%ta';
Here the above example will return all the patient names from the state ending with " ta"
Result for the above query is --
Patient ID | Patient Name | Age | Sex | State |
3 | Ben | 29 | Male | Atlanta |
8 | Cameron | 37 | Male | Minnesota |
11 | Tony | 35 | Male | Atlanta |
SELECT * FROM Patient Name
WHERE State LIKE '%Ne%';
Here it finds any value from the State with "Ne" value in it.
Result for the above query is --
Patient ID | Patient Name | Age | Sex | State |
1 | John | 45 | Male | New York |
5 | Susan | 40 | Female | New Jersey |
7 | Greg | 32 | Male | Tennesse |
8 | Cameron | 37 | Male | Minnesota |
12 | Gabriella | 39 | Female | New Mexico |
SELECT * FROM Patient Name
WHERE State LIKE 'N%a';
Here in the above example, it finds the value that starts with "N" and ends with "a"
Result for the above query is --
Patient ID | Patient Name | Age | Sex | State |
2 | Diana | 33 | Female | Nevada |
4 | Valerie | 39 | Female | Nebraska |
2. Working with the _ Wild card operator
SELECT * FROM Patient Name
WHERE State LIKE '_lorida'
Here in the above example, it will return all the patients name of the state starting with a single character but ending with "lorida"
Result for the above query is --
Patient ID | Patient Name | Age | Sex | State |
6 | Mary | 43 | Female | Florida |
11 | Kevin | 37 | Male | Florida |
3. Working with the [] wild card operator
SELECT * FROM Patient Name
WHERE State LIKE '[NAG]%';
In the above example, it will return all the patient name from the state whose name begins with either "N", "A", or "G"
Result for the above query is --
Patient ID | Patient Name | Age | Sex | State |
1 | John | 45 | Male | New York |
2 | Diana | 33 | Female | Nevada |
3 | Ben | 29 | Male | Atlanta |
4 | Valerie | 39 | Female | Nebraska |
4. Working with the ^ wild card operator
The caret is the negative wildcard character. It is used to find strings that do not match a particular pattern.
For example,
WHERE Patient name LIKE 'An[^u]%
This will return all the patient name starting with "An" and where the following letter is not "u"
Result for the above query is --