SQL wildcards are used for searching data's in a database, which were used with LIKE operator. The SQL wildcards substitute one or more character, whenever search requirements arise for data in a table of the database.
|
Wildcard |
Description |
|
% |
For zero or more characters |
|
- |
For exactly one character |
|
[ ] |
match any one characters in the specified in the location |
|
^ (or) ! |
Any single character not within the specified range |
Examples for SQL Wildcards
Just assume here, we have a SQL Table named as 'EMPLOYEE' and it has the following data as below.
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1000 |
DEEPAK |
GOTHARI |
India |
I |
20- 08-1996 |
|
E1001 |
NIRMALA |
RAJAN |
India |
II |
13-01-2000 |
|
E1002 |
ALBERT |
THOMAS |
USA |
I |
24-09-2000 |
|
E1003 |
SYENDLA |
ALVA |
Canada |
I |
06-07-2006 |
|
E1004 |
JARLESS |
OLA |
UK |
III |
07-09-2008 |
|
E1005 |
HANSEN |
DOVL |
China |
III |
05-14-1998 |
|
E1006 |
MARRY |
JAAN |
USA |
II |
30-04-1995 |
Usage of % Wildcard
If you want to select the persons living in a Country which starts with "In" from the "EMPLOYEE" table, you should use the following SELECT statement:
SELECT * FROM EMPLOYEE WHERE Country LIKE 'In%'
The result will display like as below.
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1000 |
DEEPAK |
GOTHARI |
India |
I |
20- 08-1996 |
|
E1001 |
NIRMALA |
RAJAN |
India |
II |
13-01-2000 |
Next can select the persons living in a Country which ends with "da" from the "EMPLOYEE" table, for this use the following SELECT statement:
SELECT * FROM EMPLOYEE WHERE Country LIKE '%da%'
Result will be as follows.
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1003 |
SYENDLA |
ALVA |
Canada |
I |
06-07-2006 |
Next you can select the persons living in a Country which ends with "a" from the "EMPLOYEE" table, for this use the following SELECT statement:
SELECT * FROM EMPLOYEE WHERE Country LIKE '%a%'
Result will display entaire table due to all country name ends with 'a'.
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1000 |
DEEPAK |
GOTHARI |
India |
I |
20- 08-1996 |
|
E1001 |
NIRMALA |
RAJAN |
India |
II |
13-01-2000 |
|
E1002 |
ALBERT |
THOMAS |
USA |
I |
24-09-2000 |
|
E1003 |
SYENDLA |
ALVA |
Canada |
I |
06-07-2006 |
|
E1004 |
JARLESS |
OLA |
UK |
III |
07-09-2008 |
|
E1005 |
HANSEN |
DOVL |
China |
III |
05-14-1998 |
|
E1006 |
MARRY |
JAAN |
USA |
II |
30-04-1995 |
Usage of _ Wildcard
Now you want to select the persons with their FIRST NAME which starts with any character followed by "a" from the "EMPLOYEE" table, for this requirement you should use the following SELECT statement:
SELECT * FROM EMPLOYEE WHERE FIRSTNAME LIKE '_a%'
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1004 |
JARLESS |
OLA |
UK |
III |
07-09-2008 |
|
E1005 |
HANSEN |
DOVL |
China |
III |
05-14-1998 |
|
E1006 |
MARRY |
JAAN |
USA |
II |
30-04-1995 |
Usage of [ ] Wildcard
One set of characters specified between [ ] wildcard that will match any one characters in the specified in the location of the wildcard.
Now you want to select the persons with their FIRST NAME will match any one characters from the "EMPLOYEE" table, for this requirement you should use the following SELECT statement:
SELECT * FROM EMPLOYEE WHERE FIRSTNAME LIKE '[JA]%'
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1002 |
ALBERT |
THOMAS |
USA |
I |
24-09-2000 |
|
E1004 |
JARLESS |
OLA |
UK |
III |
07-09-2008 |
Usage of ^ Wildcard
Any single character not within the specified range ([^F-P]) or set ([^MNOPQ])
Now you want to select the persons with their FIRST NAME will not match the character 'MA' from the "EMPLOYEE" table, for this requirement you should use the following SELECT statement:
SELECT * FROM EMPLOYEE WHERE FIRSTNAME LIKE '[^MA]%'
|
EMPLOYEE ID |
FIRST NAME |
LAST NAME |
COUNTRY |
GRADE |
JIONED DATE |
|
E1000 |
DEEPAK |
GOTHARI |
India |
I |
20- 08-1996 |
|
E1001 |
NIRMALA |
RAJAN |
India |
II |
13-01-2000 |
|
E1003 |
SYENDLA |
ALVA |
Canada |
I |
06-07-2006 |
|
E1004 |
JARLESS |
OLA |
UK |
III |
07-09-2008 |
|
E1005 |
HANSEN |
DOVL |
China |
III |
05-14-1998 |
Note: And if you want to join FIRSTNAME and LASTNAME Coolum use this SELECT statement.
SELECT (FIRSTNAME+' '+LASTNAME) AS EMPLOYEENAME FROM EMPLOYEE