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.

Symbol	Description	Example
%	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 (_).

Pattern	Description
'J%'	A value that start with "J".
'%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.
'J%n'	A value that start with "J" and ends with "n".

Example:

Consider a database tables called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

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:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800
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:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800
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:

EmpID Name City Age Salary

2 Marry New York 24 2750

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000
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:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800

6 Huang Beijing 28 2800
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:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800

6 Huang Beijing 28 2800