SQL LIKE Operator

Introduction to SQL LIKE

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is often used with wildcards to perform flexible matches.


SELECT * FROM Customers WHERE CustomerName LIKE 'A%';

This example selects all customers whose names start with the letter "A".

Using Wildcards with LIKE

Wildcards are characters used to substitute for any other character(s) in a string. The two main wildcards used with LIKE are the percentage sign (%) and the underscore (_).


SELECT * FROM Products WHERE ProductName LIKE '%cake%';

This example selects all products with the word "cake" anywhere in the product name.

LIKE with Underscore

The underscore (_) wildcard represents a single character. It is useful for finding strings with specific lengths or patterns.


SELECT * FROM Employees WHERE FirstName LIKE '_a%';

This query selects all employees whose first names have "a" as the second letter.

Combining LIKE with NOT

The NOT LIKE operator is used to exclude records that match a specified pattern.


SELECT * FROM Orders WHERE OrderID NOT LIKE '10%';

This example selects all orders where the OrderID does not start with "10".

LIKE with Multiple Patterns

Using multiple LIKE patterns in a query can help refine searches by combining different conditions.


SELECT * FROM Customers WHERE CustomerName LIKE 'A%' OR CustomerName LIKE '%y';

This query selects customers whose names start with "A" or end with "y".

Case Sensitivity in LIKE

The LIKE operator's case sensitivity depends on the database system. For example, in MySQL, LIKE is case-insensitive by default.


SELECT * FROM Users WHERE Username LIKE 'admin%';

This query retrieves users with usernames starting with "admin", regardless of case.

LIKE with Escape Characters

To search for a literal wildcard character, use an escape character. This allows you to find strings containing the actual wildcard symbols.


SELECT * FROM Files WHERE FileName LIKE '%!_%' ESCAPE '!';

This query finds files with an underscore in their names, using "!" as the escape character.

Performance Considerations

Using LIKE with leading wildcards can be inefficient due to full table scans. Proper indexing and query optimization can mitigate performance issues.


SELECT * FROM Inventory WHERE ItemDescription LIKE '%widget%';

This query searches for "widget" in item descriptions, which may slow down large databases without proper indexing.