SQL Like: Wildcard Operators Tutorial
The SQL WILDCARD Operators are used in conjunction with LIKE operator to enhance the search in a table. The SQL supports two main Wild Card operators as mentioned below.
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.
SQL Wildcard Characters
| Wild Card Operator | Description |
| % (Percentage) | A substitute for zero or more characters |
| _ (Underscore) | A substitute for a single character |
Wildcard Note: MS Access uses * (asterisk) and ? (question mark) instead of % and _ respectively as WildCard operators.
Wildcard Syntax of % Operator
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE '%XXX%';
Wildcard Syntax of _ Operator
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE '_XXX_';
Wildcard Note: Here XXX is any combination of numeric or string value. One can also use any number of conditions using AND or OR operators.
SQL WildCard Examples
Database table “Employee”
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | New York | 100000 |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1003 | John | 24 | Male | London | 40000 |
| 1004 | Mile | 31 | Male | London | 70000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
| 1006 | Sophie | 29 | Female | London | 60000 |
SQL Wildcard Example Using '%' Wildcard
Display only those employees whose name ends with the letter a. SQL statement is.
SELECT *
FROM Employee
WHERE EmployeeName LIKE '%a';
Note: Here SELECT * is used to select all columns in the data base table at once. Saves time than listing each column, one by one.
MS Access equivalent of above example is
SELECT *
FROM Employee
WHERE EmployeeName LIKE '*a';
Wildcard Output: Using '%' Wildcard
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
SQL Wildcard Example Using '_' Wildcard
SELECT *
FROM Employee
WHERE Age LIKE '2_';
SELECT *
FROM Employee
WHERE EmployeeName LIKE '2?';
SQL Wildcard Output: Using '_' Wildcard
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1003 | John | 24 | Male | London | 40000 |
| 1006 | Sophie | 29 | Female | London | 60000 |
