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 --