top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

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





54 views0 comments

Recent Posts

See All

In the early stages of a visualization project, we often start with two interrelated questions: Where can I find reliable data? What does this data truly represent? Information does not magically appe

bottom of page