SQL Wildcard Characters



SQL Wildcard Characters :

  1. SQL Wildcard characters are used with SQL LIKE operator.
  2. SQL Wildcard characters are used to search pattern in the database.
  3. SQL supports two types of Wildcard Characters.

Syntax :

Syntax for the LIKE Operator is as below –

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Types of Wild Card Characters :

WildcardsDescription
Percent sign (%)Used to match one or more characters.
Underscore (_)Used to match one character.
[charlist]Used to matches only character specified within the brackets
[^charlist] or [!charlist]Used to matches only character NOT specified within the brackets

Demo Database :

Consider the following demo table –

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Arnav    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Wild Card Characters : Example #1

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE 'r%';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
+----+----------+-----+-----------+----------+

Wild Card Characters : Example #2

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE 'ra_';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
+----+----------+-----+-----------+----------+

Wild Card Characters : Example #3

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE '[ram]%';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
|  7 | Arnav    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Wild Card Characters : Example #4

Consider below query on demo database –

SQL> SELECT * FROM EMPLOYEE 
WHERE ENAME LIKE '[!ram]%';

will result into

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
+----+----------+-----+-----------+----------+