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 :

Wildcards Description
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 |
+----+----------+-----+-----------+----------+