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.
- the percent sign (%)
- the underscore (_)
the percent sign represents zero, one or multiple characters. the underscore represents a single number or character. these 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
the following table has a few examples showing the where part having different like clause with '%' and '_' operators −
| sr.no. | statement & description |
|---|---|
| 1 |
where salary like '200%' finds any values that start with 200. |
| 2 |
where salary like '%200%' finds any values that have 200 in any position. |
| 3 |
where salary like '_00%' finds any values that have 00 in the second and third positions. |
| 4 |
where salary like '2_%_%' finds any values that start with 2 and are at least 3 characters in length. |
| 5 |
where salary like '%2' finds any values that end with 2. |
| 6 |
where salary like '_2%3' finds any values that have a 2 in the second position and end with a 3. |
| 7 |
where salary like '2___3' finds any values in a five-digit number that start with 2 and end with 3. |
let us take a real example, consider the customers table having the records as shown below.
+----+----------+-----+-----------+----------+ | 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 the customers table, where the 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 | +----+----------+-----+-----------+----------+