SQL Wildcards

The SQL Wildcards allow us to retrieve data from the database using the text pattern criteria. When using wildcards we must also use the SQL LIKE operator.

Here are the following wildcards that can be used to query the database:

Wildcard	Description
%	This wildcard allow you to search for zero or more characters (most commonly used wildcard).
_	This wildcard allow you to subsitute for exactly one character
[charlist]	A list of character to be subsituted with.
[^charlist	Any single chacater that has not been specified in the charlist.

Table: Student

StudentID	LastName	FirstName	DOB	CourseFees	CourseName
1	John	Astone	07/15/1976	1000.00	Accountant
2	Bob	Eastwood	02/13/1935	1500.00	Economic
3	Jane	Hollywood	03/23/1939	2000.00	IT
4	Bob	Eastwood	03/19/1980	3000.00	Economic

Example #1

Using the % Wildcard

In this example we will select all the student records with their first name begins with East.

    SELECT * FROM Student
    WHERE FirstName LIKE 'east%' 

RESULT:

StudentID	LastName	FirstName	DOB	CourseFees	CourseName
2	Bob	Eastwood	02/13/1935	1500.00	Economic
4	Bob	Eastwood	03/19/1980	3000.00	Economic

In this example we will select all the student records with their first name ends with wood.

    SELECT * FROM Student
    WHERE FirstName LIKE '%wood' 

RESULT:

StudentID	LastName	FirstName	DOB	CourseFees	CourseName
2	Bob	Eastwood	02/13/1935	1500.00	Economic
3	Jane	Hollywood	03/23/1939	2000.00	IT
4	Bob	Eastwood	03/19/1980	3000.00	Economic

Select all student records where the last name has the charactor o.

SELECT * FROM Student
WHERE LastName LIKE '%o%'

RESULT:

StudentID	LastName	FirstName	DOB	CourseFees	CourseName
1	John	Astone	07/15/1976	1000.00	Accountant
2	Bob	Eastwood	02/13/1935	1500.00	Economic
4	Bob	Eastwood	03/19/1980	3000.00	Economic

Example #2

Using the _ Wildcard

The following example will demonstrates how to select all the student records where the lastname starts with any character and follows by "ob".

    SELECT * FROM Student
    WHERE LastName LIKE '_ob'
RESULT:


StudentID
LastName
FirstName
DOB
CourseFees
CourseName

2
Bob
Eastwood
02/13/1935
1500.00
Economic

4
Bob
Eastwood
03/19/1980
3000.00
Economic


In this example we will select all the student record where their last name starts with any character then follows by "oh" and then any character after "oh".
 
    SELECT * FROM Student
    WHERE LastName LIKE '_oh_' 
RESULT:



StudentID
LastName
FirstName
DOB
CourseFees
CourseName

1
John
Astone
07/15/1976
1000.00
Accountant


  
Example #3

Using the [charlist] Wildcard

The following example will show you how to select all the student records where the first name starts with "A" or "H".
    SELECT * FROM Student
    WHERE FirstName LIKE '[A,H]%' 

RESULT:

StudentID	LastName	FirstName	DOB	CourseFees	CourseName
1	John	Astone	07/15/1976	1000.00	Accountant
3	Jane	Hollywood	03/23/1939	2000.00	IT

If you want to select all the student records where their first name is not starts with "A" or "H" then you would use the following statement:

SELECT * FROM Student
WHERE FirstName LIKE '[^A,H]%'

RESULT: