SQL Condition - LIKE

The LIKE operator enables pattern matching on character data. It is used within a WHERE clause to filter rows based on wildcard patterns. Understanding LIKE is key for searching text fields effectively.

1. Basic Wildcards

1.1 Starts With

SELECT first_name
FROM employees
WHERE first_name LIKE 'A%';

Explanation: Returns names beginning with 'A'. The pattern 'A%' allows any suffix after 'A'.

1.2 Ends With

SELECT email
FROM customers
WHERE email LIKE '%@example.com';

Explanation: Fetches emails ending in '@example.com'. Leading '%' matches any prefix.

1.3 Contains

SELECT product_name
FROM products
WHERE product_name LIKE '%pro%';

Explanation: Finds products whose name contains 'pro' anywhere.

1.4 Single Character

SELECT phone
FROM contacts
WHERE phone LIKE '123-__%-7890';

Explanation: '123-__%-7890' matches '123-' then any two chars, then any text, then '-7890'. Useful for fixed-format fields.

2. Escaping Wildcards

To search for literal '%' or '_', define an escape character:

SELECT note
FROM logs
WHERE note LIKE '%!_%' ESCAPE '!';

Explanation: The pattern '%!_%' ESCAPE '!' matches any text ending with literal '_'. '!' signals escape of the next character.

3. Case Sensitivity

Some databases treat LIKE as case-sensitive by default; use ILIKE for case-insensitive matches in PostgreSQL, or specify a case-insensitive collation in MySQL.

-- PostgreSQL case-insensitive
SELECT name
FROM users
WHERE name ILIKE 'a%';

-- MySQL explicit collate
SELECT name
FROM users
WHERE name LIKE 'a%' COLLATE utf8_general_ci;

4. Combining with Other Conditions

SELECT first_name, last_name
FROM employees
WHERE (first_name LIKE 'J%' OR last_name LIKE 'S%')
  AND hire_date >= '2024-01-01';

Explanation: Finds employees with names starting with 'J' or 'S' hired this year.

5. Performance Tips

6. Advanced Patterns

6.1 Character Classes (SQL Server)

SELECT name
FROM products
WHERE name LIKE '[A-C]%';

Explanation: Matches names starting with A, B, or C.

6.2 Range of Characters

SELECT code
FROM inventory
WHERE code LIKE '[0-9][0-9][A-Z]%';

Explanation: Matches codes starting with two digits followed by an uppercase letter.

7. Alternatives and Extensions

-- MySQL regex
SELECT name FROM users WHERE name REGEXP '^A.*son$';

-- PostgreSQL SIMILAR TO
SELECT name FROM users WHERE name SIMILAR TO 'A(_|n)%';

8. Summary

The LIKE operator is a versatile tool for text searches. With clear examples and outputs, you can see how wildcards, escaping, and patterns work—and apply best practices for performance. Combine LIKE with other SQL features to build powerful, readable queries.