When working with SQL, there are times when we need to find records that match a specific pattern rather than an exact value. That’s where the LIKE operator comes in. It allows us to perform pattern matching in queries, making it incredibly useful for searching within text fields.
What is the SQL LIKE Operator?
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is commonly used with CHAR, VARCHAR, and TEXT fields.
Here’s the basic syntax:
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;The pattern can include wildcard characters, which allow for flexible matching.
Wildcard Characters Used with LIKE
SQL uses two main wildcard characters with the LIKE operator:
%(Percent Sign): Represents zero, one, or multiple characters._(Underscore): Represents exactly one character.
Examples of Using LIKE in SQL
1. Using % for Partial Matches
If we want to find all customers whose names start with “Jo”, we can write:
SELECT *
FROM customers
WHERE name LIKE 'Jo%';Similarly, if we want to find all names that end with “son”, we can use:
SELECT *
FROM customers
WHERE name LIKE '%son';2. Using _ for Single Character Matching
If we need to find names that have exactly four letters and start with “J”, we can write:
SELECT *
FROM customers
WHERE name LIKE 'J___';3. Combining % and _
To find names starting with “J” and ending with “n” but having exactly five characters in between:
SELECT *
FROM customers
WHERE name LIKE 'J____n';4. Searching for Words Containing a Specific String
If we want to find all product names that contain the word “phone”, we use:
SELECT *
FROM products
WHERE name LIKE '%phone%';Case Sensitivity Considerations
Whether LIKE is case-sensitive depends on the database system:
| Database | Case Sensitivity |
|---|---|
| MySQL | Case-insensitive (by default in utf8_general_ci collation) |
| PostgreSQL | Case-sensitive |
| SQL Server | Depends on collation |
How to Match Literal % and _ Characters
If you need to search for actual % or _ characters, use the ESCAPE keyword:
SELECT *
FROM files
WHERE file_name LIKE 'backup\_%' ESCAPE '\';LIKE vs. Full-Text Search
While LIKE is useful for simple pattern matching, it’s not efficient for large databases. Full-text search engines or indexing can provide better performance. Consider alternatives when dealing with big datasets.
Conclusion
The LIKE operator is a powerful tool for searching text in SQL. By using wildcards like % and _, we can perform flexible pattern matching. However, for high-performance needs, full-text search might be a better option.
Â
Other interesting article:
How STRING_SPLIT works in SQL? Best STRING_SPLIT examples