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