
- SQL - Home
- SQL - Roadmap
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL - Comments
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Cheatsheet
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - Like Operator
The SQL LIKE Operator
The SQL LIKE operator is used to search for a specified pattern in a column. It is often used in the WHERE clause of the SELECT, UPDATE, or DELETE statements to filter results based on wildcard patterns.
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,
- % is a wildcard representing zero or more characters.
- 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;
SQL Wildcards in LIKE Operator
SQL wildcards are special characters used in SQL queries to match patterns in the data.
Following are the two most common wildcards used in conjunction with the LIKE operator in MySQL database:
S.No | WildCard & Definition |
---|---|
1 |
% The percent sign represents zero, one or multiple characters. |
2 |
_ The underscore represents a single number or character. |
In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other.
Examples
The table given below has a few examples showing the WHERE clause having different LIKE operators with '%' and '_':
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. |
Using % Wildcard in SQL LIKE
The % sign represents zero or multiple characters. The '%' wildcard matches any length of a string which even includes the zero length.
Example: Find salaries starting with 200
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 VALUES (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, 'Hyderabad', 4500.00 ), (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 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Now, let us display all the records from the CUSTOMERS table, where the SALARY starts with 200:
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 |
Example: Find names ending with "sh"
The following query finds rows where the NAME ends with sh:
SELECT * FROM CUSTOMERS WHERE NAME LIKE '%sh';
This produces the following output:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
Example: Find names containing "al"
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%';
The following result is produced:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
4 | Chaitali | 25 | Mumbai | 6500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
Using _ Wildcard in SQL LIKE
The underscore (_) wild card represents a single number or character. A single '_' looks for exactly one character similar to the '%' wildcard.
Example: Names starting with K and at least 4 characters long
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___%';
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 | Hyderabad | 4500.00 |
Example: Names with "m" in the third position
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%';
We get the following result on executing the above query:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
Combining Wildcards in SQL LIKE
You can combine % and _ wildcards to create more precise patterns. The underscore _ matches exactly one character, while % matches zero or more characters.
Below are some patterns with their meanings in brief:
Pattern | Description |
---|---|
'K___%' | Name starts with K, followed by at least three characters, then any number of characters. |
'_2%3' | Any first character, second character is 2, ends with 3. |
'2___3' | Five-character value starting with 2 and ending with 3. |
Combining wildcards is useful when you know the approximate length or specific characters at certain positions but want to allow different characters in other places.
SQL LIKE with OR
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 query retrieves the records of 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';
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 |
SQL NOT LIKE
We use the NOT operator with LIKE 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 fetching all the customers whose name does not start with K:
SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE 'K%';
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 |
Escape Characters in SQL LIKE
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 the escape character, 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.
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: Find values containing literal %
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'), (54000.00, '20.34%'), (75000.00, '51.00'), (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, we are displaying all the records from the EMPLOYEE table, where the BONUS_PERCENT contains the % literal:
SELECT * FROM EMPLOYEE WHERE BONUS_PERCENT LIKE'%!%%' ESCAPE '!';
This will produce the following result:
SALARY | BONUS_PERCENT |
---|---|
54000.00 | 20.34% |
84000.00 | 56.82% |
Example: Find values starting with 2 and containing %
In here, we are retrieving the BONUS_PERCENT that starts with 2 and contains the % literal:
SELECT * FROM EMPLOYEE WHERE BONUS_PERCENT LIKE'2%!%%' ESCAPE '!';
Following result is obtained:
SALARY | BONUS_PERCENT |
---|---|
54000.00 | 20.34% |
SQL LIKE Without Using Wildcards
When the LIKE operator is used without any wildcards, it behaves like the = operator and matches the value exactly. This is sometimes done to make the pattern clearer or to allow adding wildcards later.
Syntax
Following is the syntax to use the SQL LIKE operator without using wildcards:
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE 'exact_value';
Example: Exact match for "Komal"
SELECT * FROM CUSTOMERS WHERE NAME LIKE 'Komal';
This returns only the rows where NAME is exactly Komal:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
6 | Komal | 22 | Hyderabad | 4500.00 |
Common Uses of SQL LIKE Operator
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.