Also Like

Database12 SQL12
📁 last Posts
0

DB --> LEC 7 --> Topics --> 50,51

The LIKE operator in MySQL is used in a WHERE clause to search for a specified pattern in a column's data. It’s commonly used with string data types to find matches based on partial or patterned text. Below, I’ll explain how LIKE works in MySQL, its syntax, pattern-matching mechanisms, use cases, and additional details.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
  • column_name: The column to search.
  • pattern: The pattern to match, which can include wildcards.
  • LIKE: Case-insensitive by default in MySQL, unless the column’s collation is case-sensitive.

Wildcards Used with LIKE

MySQL’s LIKE operator uses two primary wildcards for pattern matching:

  1. % (Percent Sign): Matches any sequence of zero or more characters.

    • Example: 'John%' matches "John", "Johnny", "Johnson", etc.
    • Example: '%son' matches "Jason", "Wilson", "son", etc.
    • Example: '%oh%' matches "John", "Cohen", "Alcohol", etc.

  2. _ (Underscore): Matches any single character.

    • Example: 'J_hn' matches "John" but not "Johnny".
    • Example: 'S_m_' matches "Sami" or "Sume" but not "Sam" or "Sumer".

Examples

Assume a table employees with a column name:

id name
1 John Smith
2 Jane Doe
3 Bob Johnson
4 Sammy Lee

  1. Find names starting with "J":

    SELECT name FROM employees WHERE name LIKE 'J%';

    Result: John Smith, Jane Doe

  2. Find names ending with "son":

    SELECT name FROM employees WHERE name LIKE '%son';

    Result: Bob Johnson

  3. Find names with exactly four characters in the first name:

    SELECT name FROM employees WHERE name LIKE '____ %';

    Result: John Smith, Jane Doe

  4. Find names containing "am":

    SELECT name FROM employees WHERE name LIKE '%am%';

    Result: Sammy Lee

Key Details

  1. Case Sensitivity:

    • By default, LIKE is case-insensitive in MySQL for most collations (e.g., utf8mb4_general_ci).
    • If the column uses a case-sensitive collation (e.g., utf8mb4_bin), LIKE becomes case-sensitive.
    • Example with case-sensitive collation:
      SELECT name FROM employees WHERE name LIKE 'john%'; -- No match if data is "John"

  2. Combining with NOT: Use NOT LIKE to exclude rows matching a pattern.

    SELECT name FROM employees WHERE name NOT LIKE 'J%';

    Result: Bob Johnson, Sammy Lee

  3. Escaping Special Characters: If you need to search for literal % or _, escape them with a backslash (\).

    SELECT name FROM table_name WHERE name LIKE '100\%';

    Matches "100%" but not "100abc".

    Alternatively, specify a custom escape character:

    SELECT name FROM table_name WHERE name LIKE '100!%' ESCAPE '!';

  4. Performance Considerations:

    • LIKE with a leading wildcard (e.g., '%abc') cannot use indexes efficiently, leading to full table scans.
    • LIKE without a leading wildcard (e.g., 'abc%') can leverage indexes if the column is indexed.
    • For complex pattern matching, consider REGEXP (regular expressions) instead, though it’s slower and less likely to use indexes.

  5. Combining with Other Conditions:

    SELECT name FROM employees WHERE name LIKE 'J%' AND id > 1;

    Result: Jane Doe

Common Use Cases

  • Filtering Names or Text: Find customers with specific name patterns (e.g., last names ending in "son").
  • Searching Codes: Match product codes with patterns (e.g., 'ABC-___' for codes like "ABC-123").
  • Fuzzy Search: Approximate text search before implementing full-text search solutions.

Practical Tips

  • Use EXPLAIN to check if LIKE queries use indexes.
  • For case-sensitive searches, ensure the column’s collation is appropriate or use BINARY:
    SELECT name FROM employees WHERE name LIKE BINARY 'John%';
  • Avoid overusing leading wildcards in high-performance applications.

You may like these posts

Comments