What is the LIKE operator and what wildcards does it use?

Question

Accepted Answer

24
   What is the LIKE operator and what wildcards does it use?
  Level: Basic to Intermediate — Essential for pattern-based searching in text columns.

  The LIKE operator is used in a WHERE clause to search for a specified pattern in a column, rather than an exact match. It works together with two wildcard characters.

   The Two Wildcards
  
     % (percent): Matches zero, one, or many characters.
     _ (underscore): Matches exactly one character.
  

  
     Key point: LIKE is case-insensitive by default (depends on collation). For case-sensitive matching, use a binary collation or the BINARY keyword: WHERE name LIKE BINARY 'R%'.
  

   Common Pattern Examples
  
    
      
        
          Pattern
          Meaning
          Matches
        
      
      
        
          'R%'
          Starts with R
          Rumman, Rita, Rohan
        
        
          '%an'
          Ends with "an"
          Rohan, Milan
        
        
          '%mm%'
          Contains "mm"
          Rumman, Sammy
        
        
          '_a%'
          2nd letter is "a"
          Ram, Kate
        
        
          'R____'
          R + exactly 4 chars
          Rohan, Rumia
        
      
    
  

   Escaping Wildcards
  To search for a literal % or _, use the ESCAPE clause:
-- Find values that literally contain a '%' sign
SELECT * FROM products
WHERE discount LIKE '%50\%%' ESCAPE '\';

  
     Performance note: A leading wildcard (e.g., '%an') cannot use an index, forcing a full table scan. Prefer patterns like 'R%' that allow index usage.
  

   Quick Example
-- Names starting with 'R'
SELECT * FROM employees WHERE name LIKE 'R%';

-- Emails ending in '@gmail.com'
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Names where the second character is 'a'
SELECT * FROM employees WHERE name LIKE '_a%';

-- NOT LIKE: exclude a pattern
SELECT * FROM employees WHERE name NOT LIKE 'A%';

  
     Interviewer tip: The one-liner they want — "LIKE performs pattern matching using two wildcards: % matches any number of characters and _ matches exactly one. A leading % prevents index use."

Pattern	Meaning	Matches
`'R%'`	Starts with R	Rumman, Rita, Rohan
`'%an'`	Ends with "an"	Rohan, Milan
`'%mm%'`	Contains "mm"	Rumman, Sammy
`'_a%'`	2nd letter is "a"	Ram, Kate
`'R____'`	R + exactly 4 chars	Rohan, Rumia

What is the LIKE operator and what wildcards does it use?

Answer with Explanation

What is the LIKE operator and what wildcards does it use?

The Two Wildcards

Common Pattern Examples

Escaping Wildcards

Quick Example