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.
Wildcard Characters in MySQL
| Symbol | Description | Example |
|---|---|---|
| % | Represents zero, one or multiple characters. | 'J%' represents a value that start with "J", for example - John, Jo and Jack etc. |
| _ | Represents one character. | '_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc. |
Wildcard Characters in SQL Server
| Symbol | Description | Example |
|---|---|---|
| % | Represents zero, one or multiple characters. | 'J%' represents a value that start with "J", for example - John, Jo and Jack etc. |
| _ | Represents one character. | '_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 []. | '[^ack]%' represents a value that does not start with a, c or k, for example - bat, dog or rat etc. |
Wildcard Characters in Oracle
| Symbol | Description | Example |
|---|---|---|
| % | Represents zero, one or multiple characters. | 'J%' represents a value that start with "J", for example - John, Jo and Jack etc. |
| _ | Represents one character. | '_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc. |
| ESCAPE | Allow test for literal instances of a wildcard character such as % or _ | 'J%!%' ESCAPE '!' represents a value that starts with J and ends in %, for example - John%, Jo% and Journey% etc. |
Wildcard Characters in MS Access
| Symbol | Description | Example |
|---|---|---|
| * | Represents zero, one or multiple characters. | 'J*' represents a value that start with "J", for example - John, Jo and Jack etc. |
| ? | Represents one character. | '?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 []. | '[!ack]*' represents a value that does not start with a, c or k, for example - bat, dog or rat etc. |
| # | 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 table below 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__%' | A value that start with "J" and have at least 3 characters. |
| 'J%n' | A value that start with "J" and ends with "n". |
Example:
Consider a database containing a table 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 with NOT LIKE operator: NOT LIKE operator is used as the negation of LIKE operator. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:
SELECT * FROM Employee WHERE Name NOT LIKE 'Jo%';
This will produce the result as shown below:
EmpID Name City Age Salary 2 Marry New York 24 2750 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 containing '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 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
