SQL Pattern
Matching With LIKE Clause
The
SQL LIKE clause is used to compare a value to similar values using wildcard
operators. There are two wildcards used in conjunction with the LIKE operator:
1. The percent sign (%) 2. The underscore (_)
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
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: Statement |
Description |
|
WHERE SALARY LIKE
'200%' |
Finds any values that
start with 200 |
|
WHERE SALARY LIKE
'%200%' |
Finds any values that
have 200 in any position |
|
WHERE SALARY LIKE
'_00%' |
Finds any values that
have 00 in the second and third positions |
|
WHERE SALARY LIKE
'2_%_%' |
Finds any values that
start with 2 and are at least 3 characters in length |
|
WHERE SALARY LIKE '%2'
|
Finds any values that
end with 2 |
|
WHERE SALARY LIKE
'_2%3' |
Finds any values that
have a 2 in the second position and end with a 3 |
|
WHERE SALARY LIKE
'2___3' |
Finds any values in a
five-digit number that start with 2 and end with 3 |
Example, consider the
CUSTOMERS table having 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 |
+----+-------------+------+-----------------+---------------+
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 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
+----+-------------+------+-----------------+-------------+
0 Comments