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