Summary -

In this topic, we described about the Select Wildcard with detailed example.

WILDCARD characters are used for substitute of any character in the string. WILDCARDS are used with LIKE operator. In SQL, two WILDCARD characters are used to fetch data from table. They are percentage sign (%) and underscore (_).

Wildcard Characters in MS Access -

Symbol Description
* Specifies zero or more characters
? Specifies a single character
[] Specifies any single character within the brackets
! Specifies any character not in the brackets
- Specifies a range of characters
# Specifies any single numeric character

Wildcard Characters in SQL Server -

Symbol Description
% Specifies zero or more characters
_ Specifies a single character
[] Specifies any single character within the brackets
^ Specifies any character not in the brackets
- Specifies a range of characters

Syntax for percentage WILDCARD –

Query Description
SELECT * FROM table_name WHERE column_name LIKE "xxxx%" Finds any value that starts with "xxxx". The string can be 5 characters or more than 5 characters.
SELECT * FROM table_name WHERE column_name LIKE "%xxxx" Finds any value that ending with "xxxx". The string can be 5 characters or more than 5 characters.
SELECT * FROM table_name WHERE column_name LIKE "%xxxx%" Finds any value that are having "xxxx" in middle of the string. The string can be 5 characters or more than 5 characters.

Syntax for underscore WILDCARD -

Query Description
SELECT * FROM table_name WHERE column_name LIKE "xxxx_" Finds any value that starts with "xxxx". The string should be only 5 characters.
SELECT * FROM table_name WHERE column_name LIKE "_xxxx" Finds any value that ending with "xxxx". The string should be only 5 characters.
SELECT * FROM table_name WHERE column_name LIKE "_xxxx_" Finds any value that are having "xxxx" in middle of the string. The string should be only 6 characters.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch rows whose designation starts with "xxx" using LIKE operator.

Requirement1 – Fetch employee details whose designation starts with "Dir" from employee_details. The query was as follows - 

SELECT * FROM employee_details WHERE designation LIKE "Dir%";

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000

Requirement2 - To get employee details whose emp_name end with "%p1" from employee_details. The query was as follows – 

SELECT * FROM employee_details WHERE emp_name LIKE "%p1";

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000

Requirement3 - To get employee details whose emp_name have "%mp%" from employee_details. The query was as follows – 

SELECT * FROM employee_details WHERE emp_name LIKE "%mp%";

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement4 - To get employee details whose emp_name end with "_mployee1". The query was as follows – 

SELECT * FROM employee_details WHERE emp_name LIKE "_mployee1";

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000

Requirement5 - To get employee details whose emp_name start with 'Employee_'. The query was as follows – 

SELECT * FROM employee_details WHERE emp_name LIKE "Employee_";

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement6- To get employee details whose designation have "_al_". The query was as follows - 

SELECT * FROM employee_details WHERE designation LIKE "_al_";

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000