Next Chapter »

SQL Tutorial - WILDCARDS


The 'WILDCARDS' operator is used to get a set of matching or non matching records according to the condition given in a query. It is used to retrieve a single/multiple record(s) from a table based on the condition you give.

Here the 'WILDCARD' characters are executed on the 'Employees' table. The wildcard character 'LIKE" has been explained previously so we will omit it out here.

The _ character


The '_' character helps you retrieve data from a table following a specified pattern in a column that you specify in the WHERE clause. It is a wild card character.It uses the _ sign to follow the pattern.

SQL Operator Syntax


Select column-3,column-1,column-2,... column-n
from table
where column3 like '-NNN%'

The _ character Example


The 'Employees' tables contains their respective data as shown below.

EmployeeID EmployeeName DateOfBirth DesignationID DeptID City Salary
1 Richard Hughes 4/23/1945 12:00:00 AM 1 1 New Orleans 950.00
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 2 San Francisco 800.00
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 4 New York 700.00
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 4 Salt Lake City 600.00
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 3 New York 500.00
6 Jana Rae 9/23/1976 12:00:00 AM 8 2 Houston 400.00
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 6 Long Island 650.00
8 Colin Flooks 12/29/1988 12:00:00 AM 4 2 Salt Lake City 600.00
9 Anthony Frank 2/22/1988 12:00:00 AM 9 3 San Francisco 750.00
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 2 Salt Lake City 450.00
11 Jack Martin 8/25/1985 12:00:00 AM NULL NULL Austin 500.00


Select EmployeeID, EmployeeName, DateOfBirth, city, salary
from Employees
where EmployeeName like '__n%'

Note: There are 2 _ which makes it look like a single long one. So essentially I am looking for all employee names whose first 2 letters can be anything, but the 3rd letter has to be 'n'. The '_' operator retrieves all the neccessary records from the 'Employees' table for employees whose names contain n in the 3rd character as shown below.

EmployeeID EmployeeName DateOfBirth city Salary
4 Jonathan Douglas 7/7/1971 12:00:00 AM Salt Lake City 600.00
6 Jana Rae 9/23/1976 12:00:00 AM Houston 400.00

The RANGE character


The '[ ]' character helps you retrieve data from a range of values from a table following a specified pattern in a column that you specify in the WHERE clause. It is a wild card character. It uses the [ ] sign to follow the pattern.

SQL [ ] Operator Syntax


Select column-3,column-1,column-2,... column-n
from table
where column3 like '[RANGE]'

The [ ] character Example


Select EmployeeID,EmployeeName,DateOfBirth,city, salary
from Employees
where EmployeeName like '[a-r]%'

EmployeeID EmployeeName DateOfBirth city salary
1 Richard Hughes 4/23/1945 12:00:00 AM New Orleans 950.00
4 Jonathan Douglas 7/7/1971 12:00:00 AM Salt Lake City 600.00
5 Miranda Leigh 7/19/1983 12:00:00 AM New York 500.00
6 Jana Rae 9/23/1976 12:00:00 AM Houston 400.00
7 Lita Rosanna 9/14/1982 12:00:00 AM Long Island 650.00
8 Colin Flooks 12/29/1988 12:00:00 AM Salt Lake City 600.00
9 Anthony Frank 2/22/1988 12:00:00 AM San Francisco 750.00
11 Jack Martin 8/25/1985 12:00:00 AM Austin 500.00

The '[ ]' operator retrieves all the neccessary records from the 'Employees' table for employees whose names start with 'A' upto 'R' as shown above. Note that employee names starting with 'S' and 'T' present in the employees table are not extracted as they fall beyond the range.

The RANGE EXCLUSION character


The '[^ ]' character helps you retrieve data from a range of values from a table NOT following a specified pattern in a column that you specify in the WHERE clause. It is a wild card character.It uses the [^] sign to follow the pattern.

SQL [^ ] Operator Syntax


Select column-3,column-1,column-2,... column-n
from table
where column3 like '[^RANGE]'

The [^ ] character Example


Select EmployeeID,EmployeeName,DateOfBirth,city, salary
from Employees
where EmployeeName like '[^a-r]%'

EmployeeID EmployeeName DateOfBirth city salary
2 Taryn Sinclair 3/22/1980 12:00:00 AM San Francisco 800.00
3 Ted Horowitz 3/31/1960 12:00:00 AM New York 700.00
10 Stephanie Lynn 7/30/1979 12:00:00 AM Salt Lake City 450.00

The '[^ ]' operator retrieves all the neccessary records from the 'Employees' table for employees whose names  do not start with 'A' upto 'R' as shown above. Note that employee names starting with 'S' and 'T' present in the employees table are now extracted as they fall within the range given.


Next Chapter »