# SQL - Wildcards

The SQL wildcard character is used to specify one or more character in a string. It is used with SQL LIKE clause and SQL WHERE clause to search for a specified pattern.

SymbolDescriptionExample
%Represents zero, one or multiple characters.
Note: In MS Access, asterisk (*) is used instead of percentage sign (%).
'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_Represents one character.
Note: In MS Access, question mark (?) is used instead of (_).
'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
[]Represents any single character specified within [].'[ack]%' represents a value that starts with a, c or k, for example - ant, cat or kite etc.
-Represents a range of characters.'[a-d]%' represents a value that starts with a, b, c or d, for example - ant, bat, cat or dog etc.
^Represents any character not specified within [].
Note: In MS Access, exclamation mark (!) is used instead of (^).
'[^ack]%' represents a value that does not start with a, c or k, for example - bat, dog or rat etc.
#In MS Access, represents any single numeric character.'5#5' represents a three digit number with 5 at one's and hundred's place, for example - 505, 555 or 595 etc.

The below table describes patterns which is used with LIKE operator and uses (%) and (_).

PatternDescription
'%n'A value that end with "n".
'%oh%'A value that have "oh" in any position.
'_o%'A value that have "o" in the second position.
'J_%'A value that start with "J" and have at least 2 characters.

### Example:

Consider a database tables called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

• Using the % Wildcard : To select all records of the Employee table with Name starting with 'Jo', the SQL code is given below.

SELECT * FROM Employee
WHERE Name LIKE 'Jo%';


This will produce the result as shown below:

EmpIDNameCityAgeSalary
1JohnLondon253000
3JoParis272800

• Using the _ Wildcard : To select all records of the Employee table with Name starting with 'o' as second character, the SQL code is mentioned below.

SELECT * FROM Employee
WHERE Name LIKE '_o%';


The result of the above code will be:

EmpIDNameCityAgeSalary
1JohnLondon253000
3JoParis272800

• Using the [] Wildcard : To select all records of the Employee table with Name starting with 'K', 'R' or 'M', the SQL code is:

SELECT * FROM Employee
WHERE Name LIKE '[KRM]%';


The result of the code will be:

EmpIDNameCityAgeSalary
2MarryNew York242750
4KimAmsterdam303100
5RameshNew Delhi283000

• Using the ^ Wildcard : To select all records of the Employee table with Name not starting with 'K', 'R' or 'M', the SQL code is:

SELECT * FROM Employee
WHERE Name LIKE '[^KRM]%';


The result of the code will be:

EmpIDNameCityAgeSalary
1JohnLondon253000
3JoParis272800
6HuangBeijing282800

• Using the - Wildcard : To select all records of the Employee table with Name not starting with 'G' to 'J', the SQL code is:

SELECT * FROM Employee
WHERE Name LIKE '[G-J]%';


The result produced by the code will be following:

EmpIDNameCityAgeSalary
1JohnLondon253000
3JoParis272800
6HuangBeijing282800