LIKE OPERATOR

The SQL LIKE operator is used in a WHERE clause to look for a particular pattern in a column.It 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. The symbols can be used in combinations.

SQL LIKE Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern;

Example:

Here is the EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	12,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	11,000	PUNE
8	A	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	GOA
9	B	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	DELHI

1) select details of all employees with a City starting with the letter "B":

SELECT * FROM EMPLOYEE1 WHERE CITY LIKE ‘B%’;

The output would be like:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR

NP:- The "%" sign is used to define wildcards (missing letters) both before and after the pattern.

2) Select details of all employees with a City ending with the letter "I":

SELECT * FROM EMPLOYEE1 WHERE CITY LIKE ‘%I’;

The output would be like:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI
9	B	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	DELHI

3) select details of all employees with a city containing the pattern "BA":

SELECT * FROM EMPLOYEE1 WHERE CITY LIKE ‘%BA%’;

The output would be like:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR

Few more examples:

Statement	Description
WHERE SALARY LIKE '100%'	Finds any values that start with 100
WHERE SALARY LIKE '%100%'	Finds any values that have 100 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