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". |
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 |
