Menu

Logo

SQL LIKE

Basic LIKE Usage

The LIKE operator in SQL is used to search for a specified pattern in a column, often with wildcard characters. It is commonly used in WHERE clauses for flexible text matching.

SELECT * FROM customers
WHERE name LIKE 'A%';

This query returns all rows from the customers table where the name starts with the letter "A". The % wildcard matches any sequence of characters (including none) after "A". For example, names like "Alice", "Aaron", and "Ava" would match.

You can also use other patterns with LIKE:

  • '%son': matches names ending in "son", like "Johnson"
  • '_a%': matches names with "a" as the second character, like "Jack" or "Mark"

LIKE is case-insensitive in some databases (like MySQL), but case-sensitive in others (like PostgreSQL unless configured otherwise).

More Pattern Examples

-- Ends with 'n'
SELECT * FROM employees
WHERE name LIKE '%n';

-- Contains 'or'
SELECT * FROM books
WHERE title LIKE '%or%';

-- Second letter is 'a'
SELECT * FROM animals
WHERE species LIKE '_a%';

NOT LIKE

This excludes users whose email ends with ".com".

SELECT * FROM users
WHERE email NOT LIKE '%.com';

Case Sensitivity

Pattern matching with the LIKE operator behaves differently across database systems when it comes to case sensitivity:

  • MySQL: The LIKE operator is case-insensitive by default for non-binary strings (e.g., VARCHAR, TEXT), due to the default collation settings like utf8_general_ci.
    Example: LIKE 'A%' would match "Alice", "alice", or "ALBERT".
  • PostgreSQL: The standard LIKE operator is case-sensitive. To perform a case-insensitive match, use ILIKE (which stands for "insensitive LIKE").

Here’s how a case-insensitive search is done in PostgreSQL:

-- PostgreSQL example (case-insensitive)
SELECT * FROM names
WHERE name ILIKE 'jo%';

This will return names like "John", "johnson", "JOEY", etc. Use ILIKE whenever you need to match text regardless of casing in PostgreSQL.

If you’re unsure about case behavior in your SQL database, check the documentation or collation settings. For cross-database compatibility, you can also convert both sides to lowercase using LOWER():

-- Case-insensitive match using LOWER() (works in most databases)
SELECT * FROM names
WHERE LOWER(name) LIKE 'jo%';

Best Practices

  • Use leading wildcards (e.g. %value) only when needed to avoid performance hits
  • Escape characters using double backslashes \\\\ when searching for literal symbols like '%'
  • For exact matches, use = instead of LIKE

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic