May 16, 2021 SQL
We've already discussed SQL's LIKE operator, which uses wildcards to compare two similar values.
SQL supports the following two wildcards to use with the LIKE operator:
| Wildcard | Describe |
| Percent sign (%) | Match one or more characters. Note: MS Access uses an asterisk as a wildcard to match one or more characters, not a percent sign (%). |
| Underscores | Match a character. Note: MS Access uses a question mark (?) instead of an underscore to match any character. |
A percent sign represents zero, one, or more characters. T he underscore represents a single character. These symbols can be used together.
The basic syntax for "%" 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
WHERE column LIKE '_XXXX_'You can combine multiple conditions with an AND or OR operator. Here, XXXX can be any number or string.
| Statement | Describe |
| WHERE SALARY LIKE '200%' | Find any values that start with 200. |
| WHERE SALARY LIKE '%200%' | Find any values that have 200. |
| WHERE SALARY LIKE '_00%' | Find out any values with a second position and a third position of 0. |
| WHERE SALARY LIKE '2_%_%' | Find any values that start with 2 and are at least 3 in length. |
| WHERE SALARY LIKE '%2' | Find any values that end in 2. |
| WHERE SALARY LIKE '_2%3' | Find any values with a second position of 2 and end with 3. |
| WHERE SALARY LIKE '2___3' | Find any five digits that start with 2 and end with 3. |
Let's look at a real-world example and consider the CUSTOMERS table with the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+The following example will find all salary records in the CUSTOMER table that start with 200 and show them:
SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%'; The result is as follows:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
+----+----------+-----+-----------+----------+