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

Patient ID	Patient Name	Age	Sex	State
9	Anatasia	27	Female	Texas
10	Anita	35	Female	Georgia

4. Working with the ! wild card operator

In the below example, we can select all the patient name of the state that does not begin with either "N", "A", or "T".

SELECT * FROM Patient Name
WHERE State LIKE '[!NAT]%';

The above statement can also be written as follows

SELECT * FROM Patient Name
WHERE State NOT LIKE '[NAT]%';

Result for the above query is --

​Patient ID	Patient Name	Age	Sex	State
6	Mary	43	Female	Florida
8	Cameron	37	Male	Minnesota
10	Anita	35	Female	Georgia
11	Kevin	37	Male	Florida

5. Working with the - wild card operator

SELECT * FROM Patient Name
WHERE State LIKE '[a-d]%';

The above statement will return the value of all the patient name of the state whose name begins with either "a", "b", "c" or "d"

Result for the above query is

Patient ID	Patient Name	Age	Sex	State
3	Ben	29	Male	Atlanta

Difference between wildcard operator and LIKE

• LIKE operator is used to search a defined string pattern in the column and return the matching rows.

SELECT *FROM Employee Name
WHERE NAME LIKE 's%'

The above example will bring the names of all the employees staring with "s"

• But Wild card Operators is used to search a more specific pattern in the column and return the matching rows.

SELECT * FROM Employee Name
WHERE State LIKE '%na';

Here , in the above example it will return more specified value of the employee name ending with "na"

Things to keep in mind while using Wild card operators

Wild card operator works only with the LIKE operator. If you place a wildcard inside an ordinary string that is not an argument to the LIKE operator, you will find that SQLb will treat the wildcard as a regular character appearing in the string, For example, if you see the below example, we did not use the LIKE operator in this query

SELECT * FROM Patient Name
WHERE State '%ta';

The above query will search for all the patient name that is equal to "ta" and it will return no data. So be careful while using wildcard operators.

Conclusion

I hope this blog will be helpful to understand the concept of Wildcard operators. They are similar to Regular Expressions where we search for patterns. It is commonly used when we want to search and filter in the database. Furthermore, we also learned various SQL Wildcard operators with examples to give us more understanding.

Wildcard operators are very useful for comparing two strings with the utmost precision. We can combine multiple wildcards together in a solo string to obtain better search outcomes and results. So, when you can't remember exactly how the value is spelled , try using a Wild card operator in a SQL query.

Thanks for reading !!!