SQL LIKE and Wildcards: Search Text (Starts With, Ends With, Contains)
LIKE finds text that matches a pattern. Use the percent sign for starts with, ends with and contains, and the underscore for one letter, while searching customer names in a sample online shop database.
Step by step
Search text
- Search by pattern. A caller remembers that the customer's name started with H, but nothing else. Equals can't help you here. Like can. Let's search customer names by pattern.
- The customers table. We'll search the customers table, using the first name and last name columns.
Starts with
- Starts with. Like matches a pattern. The percent sign means any letters, or none at all. So H percent means starts with H. Five customers whose last name starts with H. A short list to call back.
SELECT first_name, last_name FROM customers WHERE last_name LIKE 'H%';
Ends with
- Ends with. Move the percent sign to the front, and the pattern means ends with. Percent s finds last names ending in s. Seven last names end in s.
'%s'
Contains
- Contains. Put a percent sign on both sides, and it means contains. Any last name with the letters a n anywhere inside. Four names contain a n. In the middle, at the end, it doesn't matter.
'%an%'
One letter
- Underscore. The underscore is the other wildcard. It matches exactly one letter. Three underscores find first names that are exactly three letters long. Five short first names, three letters each.
first_name LIKE '___'
Capital letters
- Capital letters. One more thing. Let's search product names for the word set, typed in small letters. It found Set of four, with a capital S. This practice database ignores capitals in like. Some databases don't, so test your pattern before you trust it.
SELECT product_name FROM products WHERE product_name LIKE '%set%';
Recap
- LIKE in four patterns. So, like searches text. Percent at the end means starts with, at the front means ends with, on both sides means contains. And an underscore is exactly one letter.
