SQL Wildcard Operators
We already have discussed SQL LIKE operator, which is used to compare a value to similar values using wildcard operators.
SQL supports following two wildcard operators in conjunction with the LIKE operator:
The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.
Syntax:
The basic syntax of ‘%’ and ‘_’ is as follows:
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
CHAPTER 34
36
WHERE column LIKE '_XXXX_'
You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.
Example:
Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:
Let us take a real example, consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 2 | Mdhu sudhan rao | 25 | Delhi | 1500.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
| 4 | Kishor | 25 | Mumbai | 6500.00 |
| 5 | Ranjith | 27 | Bhopal | 8500.00 |
| 6 | Abhi | 22 | MP | 4500.00 |
| 7 | Kumar | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would display all the records from CUSTOMERS table where SALARY starts with 200:
SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramakrishna | 32 | Ahmedabad | 2000.00 |
| 3 | Sai | 23 | Kota | 2000.00 |
+----+----------+-----+-----------+----------
