SQL Commands


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 OperatorDescription
% (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 IDEmployee NameAgeGenderLocationSalary
1001Henry54MaleNew York100000
1002Tina36FemaleMoscow80000
1003John24MaleLondon40000
1004Mile31MaleLondon70000
1005Tara26FemaleMoscow50000
1006 Sophie29FemaleLondon60000

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 IDEmployee NameAgeGenderLocationSalary
1002Tina36FemaleMoscow80000
1005Tara26FemaleMoscow50000

SQL Wildcard Example Using '_' Wildcard

SELECT *
FROM Employee
WHERE Age LIKE '2_';

MS Access equivalent of above example is

SELECT *
FROM Employee
WHERE EmployeeName LIKE '2?';

SQL Wildcard Output: Using '_' Wildcard

Employee IDEmployee NameAgeGenderLocationSalary
1003John24MaleLondon40000
1006Sophie29FemaleLondon60000


BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish