SQL - LIKE Operator

---

The SQL LIKE is a logical operator that is used to retrieve the data in a column of a table, based on a specified pattern.

It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using ‘Wildcards’.

Suppose we need to submit the list of all the students whose name starts with ‘K’. We can obtain this with the help of the LIKE operator as follows −

WHERE student_name LIKE 'K%';

Here, the “%” is a wild card which represents zero, one or multiple characters. And the expression “K%” specifies that it will display the list of all the students whose name starts with ‘k’.

The LIKE operator can be used with strings, numbers, or date values. However, using the string values is recommended.

Syntax

The basic syntax of the SQL LIKE operator is as follows −

SELECT column1, column2, ...
FROM table_name
WHERE columnn LIKE specified_pattern;

What are wild cards?

SQL wildcards are special characters used in SQL queries to match patterns in the data. Following are the four wildcards used in conjunction with the LIKE operator −

S.No	WildCard & Definition
1	% The percent sign represents zero, one or multiple characters.
2	_ The underscore represents a single number or character.
3	[] This matches any single character within the given range in the [].
4	[^] This matches any single character excluding the given range in the [^].

Note − In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ‘%’ and ‘_’.

The table given below has a few examples showing the WHERE clause having different LIKE operators with '%' , '_' , [] and [^] pattern −

S.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.
8	WHERE NAME LIKE 'A[a, l, j, y, a, k]' Finds any name that starts with A and has the specified alphabets. For e.g. Ajay
9	WHERE NAME LIKE 'A[a, l, j, y, a, k]' Finds any name that starts with A and does not consists of the specified alphabets. For e.g. Abdu.

Using the “%” Wildcard character

The % sign represents zero or multiple characters. The ‘%’ wildcard matches any length of a string which even includes the zero length.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+

Now, let us try to display all the records from the CUSTOMERS table, where the SALARY starts with 200.

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

Output

This would produce the following result −

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  | 
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 | 
|  3 | kaushik |  23 | Kota      | 2000.00 | 
+----+---------+-----+-----------+---------+

Example

Below is the query, that displays all the records from the CUSTOMERS table previously created, with the NAME that has ‘al’ in any position. Here we are using multiple ‘%’ wildcards in the LIKE condition −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE '%al%';

Output

The following result is produced −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  | 
+----+----------+-----+---------+---------+
|  4 | Chaitali |  25 | Mumbai  | 6500.00 | 
|  6 | Komal    |  22 | MP      | 4500.00 | 
+----+----------+-----+---------+---------+

Using the “_” wildcard character

The underscore wild card represents a single number or character. A single ‘_’ looks for exactly one character similar to the ‘%’ wildcard.

Example

Following is the query, which would display all the records from the CUSTOMERS table previously created, where the Name starts with K and is at least 4 characters in length −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'K___%';

Output

The result obtained is given below −

+----+---------+-----+---------+---------+
| ID | NAME    | AGE | ADDRESS | SALARY  | 
+----+---------+-----+---------+---------+
|  2 | Khilan  |  25 | Delhi   | 1500.00 | 
|  3 | kaushik |  23 | Kota    | 2000.00 | 
|  6 | Komal   |  22 | MP      | 4500.00 | 
+----+---------+-----+---------+---------+

Example

Following is the query to display all the records from the CUSTOMERS table, where the Name has ‘m’ in the third position −

SELECT * from customers WHERE NAME LIKE '__m%';

Output

We get the following result on executing the above query −

+----+--------+-----+-----------+---------+
| ID | NAME   | AGE | ADDRESS   | SALARY  | 
+----+--------+-----+-----------+---------+
|  1 | Ramesh |  32 | Ahmedabad | 2000.00 | 
|  6 | Komal  |  22 | MP        | 4500.00 | 
+----+--------+-----+-----------+---------+

Using the “[ ]” wildcard character

The square bracket with a list of characters matches any single character within the given range [b-k] or set [xyz].

Example

In the query given below we are trying to display all the records from the CUSTOMERS table, where the NAME starts with K and has the specified characters set [h,i,o,m,l,a,n] −

select * from customers 
where NAME LIKE 'k[h,i,o,m,l,a,n]%';

Output

We get the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

Example

In here, we are displaying all the records from the CUSTOMERS table, where the first and last character in the NAME lies anywhere in the range [b-i] −

select * from customers 
where NAME LIKE '[b-i]%';

Output

The result given below is displayed −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
+----+----------+-----+-----------+----------+

Using the “[^]” wildcard character

The [^ character list or range] matches any single character that is not present in the given range or character list.

Example

In the query given below, the first character in the NAME column is not in the range of [b-k] −

select * from customers 
where NAME LIKE '[^b-k]%';

Output

The following result is obtained −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  7 | Muffy    |  24 | Indore    |  10000.00|
+----+----------+-----+-----------+----------+

Using LIKE operator with OR operator

We can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.

Syntax

Following is the basic syntax of using LIKE operator with OR operator −

SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE pattern1 OR column2 LIKE pattern2 OR ...;

Example

Here, the SQL command select the customers whose NAME starts with C and ends with i, or customers whose NAME ends with k −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'C%i' OR NAME LIKE '%k';

Output

This will produce the following result −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  | 
+----+----------+-----+---------+---------+
|  3 | kaushik  |  23 | Kota    | 2000.00 |              
|  4 | Chaitali |  25 | Mumbai  | 6500.00 |               
|  5 | Hardik   |  27 | Bhopal  | 8500.00 |                
+----+----------+-----+---------+---------+	

Using NOT operator with the LIKE condition

We use the NOT LIKE operator to extract the rows which does not contain a particular string provided in the search pattern.

Syntax

Following is the basic syntax of NOT LIKE operator in SQL −

SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;

Example

In the query given below we are trying to fetch all the customers whose NAME does not start with K −

select * from customers
WHERE NAME NOT LIKE 'K%';

Output

This will produce the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Using Escape characters with LIKE operator

The escape character in SQL is used to exclude certain wildcard characters from the expression of the LIKE operator. By doing so, we can use these characters in their general sense.

Using escape, we can also avoid using the characters that are reserved in SQL syntax to denote specific commands, such as the single quote “ ' ”, “%” and “_”.

For example, if you need to search for “%” as a literal in the LIKE condition, then it is done using Escape character.

Note − An escape character is only defined as a single character. It is suggested to choose the character which is not present in our data.

Syntax

The syntax for using the LIKE operator with escape characters is as follows −

SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE 'pattern ESCAPE escape_character';

Where,

• pattern is the pattern you want to match.

• ESCAPE is the keyword that indicates the escape character

• escape_character is the character that you want to use as the escape character.

Example

Let us create a new table EMPLOYEE using the query below −

CREATE TABLE EMPLOYEE (
   SALARY DECIMAL (18,2) NOT NULL,
   BONUS_PERCENT VARCHAR (20)
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO EMPLOYEE VALUES (67000.00, '45.00');  
INSERT INTO EMPLOYEE VALUES (54000.00, '20.34%');     
INSERT INTO EMPLOYEE VALUES (75000.00, '51.00');         
INSERT INTO EMPLOYEE VALUES (84000.00, '56.82%');

The Employee table consists of the salary of employees in an organization and the bonus percentage in their salary as shown below −

+----------+---------------+
| SALARY   | BONUS_PERCENT |
+----------+---------------+
| 67000.00 | 45.00         |
| 54000.00 | 20.34%        |
| 75000.00 | 51.00         |
| 84000.00 | 56.82%        |
+----------+---------------+

Now, let us try to display all the records from the EMPLOYEE table, where the BONUS_PERCENT contains the % literal.

select * from CUSTOMERS WHERE BONUS_PERCENT LIKE'%!%%' escape '!';

Output

This will produce the following result −

+----------+---------------+
| SALARY   | BONUS_PERCENT |
+----------+---------------+
| 54000.00 | 20.34%        |
| 84000.00 | 56.82%        |
+----------+---------------+

Example

In here, we are trying to return the BONUS_PERCENT that starts with ‘2’ and contains the ‘%’ literal.

select * from CUSTOMERS
WHERE BONUS_PERCENT LIKE'2%!%%' escape '!';

Output

Following result is obtained −

+----------+---------------+
| SALARY   | BONUS_PERCENT |
+----------+---------------+
| 54000.00 | 20.34%        |
+----------+---------------+

Uses of LIKE Operator in SQL

The few uses of LIKE operators are given below −

• It helps us to extract data that matches with the required pattern.

• It helps us in performing complex regex-based queries on our data.

• It simplifies the complex queries.