LIKE Operator in SQL : Syntax & Wildcard Search with FAQs
In SQL, LIKE is an operator. LIKE operator is used in SQL to match a specified pattern within a column containing text data. LIKE supports the use of wildcard characters ( % and _) for pattern matching. Understanding the LIKE operator enhances the ability to perform complex searches, retrieve targeted data, and extract valuable insights from datasets. This article provides a detailed overview of the LIKE operator with example. After reading this article you can use the LIKE operator easily and retrieve valuable data from a table.
Syntax
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
When using the LIKE operator in SQL, two commonly used wildcards are
- % (percent sign) represents zero, one, or multiple characters.
- _ (underscore sign) represents exactly one single character.
Demo Database
CREATE TABLE `Employees` (
`Id` INT PRIMARY KEY AUTO_INCREMENT,
`Name` VARCHAR(50) NOT NULL,
`Mobile` VARCHAR(20) NOT NULL,
`JobTitle` VARCHAR(100) NOT NULL,
`Department` VARCHAR(100) NOT NULL,
`salary` DECIMAL(10, 2)
);INSERT INTO Employees (Name, Mobile, JobTitle, Department, salary)
VALUES
('Ram', '9999999998', 'Software Engineer III', 'Engineering', 590000.00),
('Gopal', '9999999958', 'Marketing Manager', 'Marketing', 10000.00),
('Deepak', '9999999238', 'Software Engineer II', 'Engineering', 130000.00),
('Jone', '9999999538', 'Software Engineer', 'Engineering', 30000.00),
('Roopa', '9999994238', 'Software Engineer II', 'Engineering', 120000.00),
('Jack', '9999999128', 'Sr. Marketing Manager', 'Marketing', 130000.00),
('Deepak Sharma', '9999945238', 'Software Engineer II', 'Engineering', 230000.00),
('Reena', '9999999698', 'Data Analyst', 'Analytics', 80000.00),
('Rina', '9999999898', 'Data Analyst', 'Analytics', 90000.00),
('Jhon', '9999999978', 'HR Specialist', 'Human Resources', 75000.00);LIKE with %
if you need to match zero or more than zero characters you can use % ( percent sign) wildcard in search pattern. The % wildcard is used to match any sequence of characters (including zero characters) in a search string. This flexibility enables comprehensive searches based on specified criteria.
SQL query to retrieve employees whose name start with `de`.
SELECT Id,Name FROM Employees WHERE Name LIKE "de%";
Id Name ----------------------- 3 Deepak 7 Deepak Sharma
SQL query to retrieve employees whose name end with `k`.
SELECT Id, Name FROM Employees WHERE Name LIKE "%k";
Id Name --------------------- 3 Deepak 6 Jack
SQL query to retrieve employees whose name contains `pa`.
SELECT Id, Name FROM Employees WHERE Name LIKE "%pa%";
Id Name -------------------- 3 Deepak 5 Gopal 7 Deepak Sharma
SQL query to retrieve employees whose name start with `r` and end with `a`.
SELECT Id, Name FROM Employees WHERE Name LIKE "r%a";
Id Name ----------------- 5 Roopa 8 Reena 9 Rina
LIKE with _ (underscore sign)
if you need to match exact one character you should use _ ( underscore sign) wildcard in search pattern. The _ (underscore sign) wildcard is used to match exact one character in a search string. This flexibility enables comprehensive searches based on specified criteria.
SQL query to retrieve employees whose names have exactly 4 characters.
SELECT Id, Name
FROM Employees
WHERE Name LIKE "____"; // continues 4 _
OR
SELECT Id, Name
FROM Employees
WHERE LENGTH(Name) = 4;Id Name ---------------- 4 Jone 6 Jack 9 Rina 10 Jhon
SQL query to retrieve employees whose names have exactly 4 characters and start with `J`.
SELECT Id, Name FROM Employees WHERE Name LIKE "J___";
Id Name --------------- 4 Jone 6 Jack 10 Jhon
Combine % and _ with LIKE
In SQL, wildcards such as % and _ can be used in combination with each other to create flexible and powerful search patterns.
SQL query to retrieve employees whose names start with `r` and have at least 4 characters.
SELECT Id, Name FROM Employees WHERE Name LIKE "r___%";
Id Name ----------------- 5 Roopa 8 Reena 9 Rina
LIKE Without Wildcard
In SQL, while the LIKE operator is typically used with wildcard characters (% and _) for pattern matching in searches. It can also be used effectively without any wildcard to match exact strings.
SQL query to retrieve employees whose name is `ram`.
SELECT Id, Name FROM Employees WHERE Name LIKE "ram";
Id Name ---------------- 1 Ram
Indexing With LIKE
In SQL, Creating indexes on columns used with the LIKE operator can enhance query performance but it's crucial to understand how indexes work with LIKE queries to optimize database performance effectively.
Prefix Search Optimization
Indexes are most effective when the LIKE pattern has a constant prefix followed by a wildcard (prefix%). This configuration allows the database engine to efficiently utilize the index for quick lookup and retrieval of matching rows.
Example :- An index on column_name can efficiently support queries like WHERE column_name LIKE 'prefix%'
Leading Wildcards
Leading wildcards (%suffix) generally prevent efficient index usage because they force the database to scan the entire index or table to find matching rows. This can lead to slower query performance, especially on large datasets.
Different database management systems may vary in how they handle and optimize LIKE queries with indexes. refer to the documentation provided by your database vendor. Understanding these can help optimize query performance effectively based on your database's capabilities and configurations. Test and analyze query performance to ensure indexes are effectively supporting your LIKE queries. Use database tools and query execution plans to monitor index usage.
Key Characteristics of LIKE Operator
- LIKE operator is used in SQL to match a specified pattern within a column containing text data.
- LIKE supports the use of wildcard characters ( % and _) for pattern matching.
- LIKE Can be used without wildcards to find exact matches of strings or patterns.
- LIKE operator enhances the ability to perform complex searches, retrieve targeted data, and extract valuable insights from datasets
- LIKE operator can efficiently filter large datasets, especially when combined with indexes on the columns being searched.
- Overuse of leading wildcards (%text) can lead to slower query performance as it prevents efficient use of indexes.
- Test and analyze query performance to ensure indexes are effectively supporting your LIKE queries. Use database tools and query execution plans to monitor index usage.
Frequently Asked Questions (FAQs)
1. What is the LIKE operator in SQL?
The LIKE operator in SQL is used to search for patterns within text-based columns. It allows you to match partial values instead of exact matches by using wildcard characters. LIKE is especially useful when you don’t know the full value but want to search based on prefixes, suffixes or substrings.
2. What are the wildcard characters used with LIKE?
SQL supports two main wildcards with LIKE:
- % matches zero, one or multiple characters
- _ matches exactly one character
These wildcards make LIKE powerful for flexible text searches, such as finding names that start with, end with or contain specific characters.
3. What is the difference between % and _ in LIKE?
The % wildcard is flexible and matches any number of characters, including none. The _ wildcard is strict and matches exactly one character. Choosing the correct wildcard is important to avoid returning unintended results, especially in validation or reporting queries.
4. Can LIKE be used without wildcards?
Yes. LIKE can be used without % or _, but in that case it behaves similarly to an equality (=) comparison. However, depending on the database, LIKE may still be case-insensitive or follow collation rules, making it slightly different from = in behavior.
5. Is LIKE case-sensitive?
Case sensitivity depends on the database system and column collation. For example, MySQL is usually case-insensitive by default, while PostgreSQL is case-sensitive unless you use ILIKE. Always verify collation rules to avoid unexpected results when using LIKE.
6. Can LIKE be combined with WHERE AND OR clauses?
Yes. LIKE is most commonly used inside WHERE clauses and can be combined with AND, OR and NOT operators. When mixing conditions, always use parentheses to ensure correct logical grouping and avoid ambiguity caused by operator precedence.
7. How does LIKE handle NULL values?
LIKE comparisons with NULL always result in UNKNOWN, not TRUE. This means rows with NULL values in the searched column are excluded from results. If NULL values matter, you must explicitly handle them using IS NULL or COALESCE.
8. Can LIKE be used with numeric columns?
LIKE is designed for text-based columns. Numeric columns must be converted to strings before using LIKE, which is usually inefficient and discouraged. For numeric comparisons, use standard comparison operators instead of LIKE.
9. How does LIKE impact query performance?
LIKE can impact performance, especially on large tables. Queries with leading wildcards (%text) often cause full table scans and bypass indexes. Proper indexing and avoiding leading wildcards where possible helps maintain good performance.
10. Can indexes be used with LIKE queries?
Indexes can be used efficiently when the LIKE pattern starts with a constant prefix, such as 'abc%'. In contrast, patterns starting with % generally prevent index usage. Understanding this behavior is critical for optimizing text search queries.
11. What is prefix search optimization with LIKE?
Prefix search refers to patterns like 'prefix%'. These allow the database engine to quickly narrow down results using indexes. Prefix searches are the most performant way to use LIKE and are widely recommended in production systems.
12. Is LIKE suitable for advanced text search?
LIKE is suitable for basic pattern matching but not for complex text search. For advanced requirements such as ranking, stemming or fuzzy matching, full-text search features or specialized search engines are more appropriate.
13. Is LIKE commonly used in SQL interviews?
Yes. LIKE is a frequent interview topic. Interviewers often ask about wildcard behavior, performance implications, case sensitivity and differences between LIKE and equality operators. Real-world understanding of indexing with LIKE is highly valued.
14. What are common mistakes developers make with LIKE?
Common mistakes include overusing leading wildcards, ignoring case sensitivity rules, forgetting NULL handling and using LIKE on non-text columns. These mistakes can lead to incorrect results or poor query performance.
15. When should LIKE be avoided?
LIKE should be avoided when exact matches are needed, when performance is critical on large datasets with leading wildcards or when numeric or structured data is involved. In such cases, use equality checks, range filters or full-text search instead.