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 a customer's name started with H, but nothing else. = only finds an exact value, so it can't help. LIKE finds text that matches a pattern.
- The customers table. We search the customers table, using its first_name and last_name columns.
Starts with
- Starts with. Type SELECT first_name, last_name FROM customers WHERE last_name LIKE 'H%'; The % sign stands for any characters, or none at all, so 'H%' means starts with H. Run it. It returns 5 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. Replace 'H%' with '%s'. With the % sign at the front, the pattern means ends with, so it finds last names ending in s. Run it. 7 last names end in s.
'%s'
Contains
- Contains. Replace '%s' with '%an%'. A % sign on both sides means contains, so it finds any last name with the letters an anywhere inside. Run it. 4 names contain an. It doesn't matter if the letters sit in the middle or at the end.
'%an%'
One letter
- Underscore. Now swap the condition for first_name LIKE '___'. The underscore is the other wildcard and matches exactly one character, so 3 underscores find first names exactly 3 letters long. Run it. It returns 5 first names, each 3 letters long.
first_name LIKE '___'
Capital letters
- Capital letters. Clear the editor and search the products table: SELECT product_name FROM products WHERE product_name LIKE '%set%'; The word set is typed in small letters. Run it. It finds 1 product, Set of four, with a capital S, because this practice database ignores capitals in LIKE. Some databases, such as PostgreSQL, 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. LIKE searches text. % at the end means starts with, % at the front means ends with, and % on both sides means contains. _ matches exactly one character.
Common mistakes
- Using = with a wildcard. WHERE last_name = 'H%' looks for the actual text H%, so it usually returns nothing. Wildcards only work with LIKE.
- Putting the % on only one side. 'an%' means starts with an, not contains an. To find the letters anywhere in the text, put a % on both sides: '%an%'.
- Assuming LIKE always ignores capitals. It depends on the database. In PostgreSQL, LIKE treats capitals as different, so use ILIKE or compare LOWER(product_name) with a lower-case pattern.
- Searching for a real % or _. In a pattern, % and _ are wildcards, so '%10%%' matches any text containing 10, not just 10%. Pick an escape character and declare it, like LIKE '%10!%%' ESCAPE '!'.
When a business analyst uses this
In a requirements workshop, a stakeholder half-remembers a customer's name. WHERE last_name LIKE 'H%' gives a short list to check together on screen. Before signing off a data migration, LIKE also helps you count records that still use an old code, such as product codes starting with a retired prefix.
Questions people ask
How do you escape a wildcard in SQL LIKE?
Choose an escape character, put it before the % or _ you want to treat as plain text, and declare it with ESCAPE. For example, LIKE '%10!%%' ESCAPE '!' finds text that contains 10%.
Can you use multiple wildcards in one LIKE pattern?
Yes. '%an%' uses 2 % signs, and you can mix both wildcards, like '_a%' for text whose second letter is a. To match several different patterns, join LIKE conditions with OR.
What does LIKE do without wildcards?
It only matches text that is exactly the same as the pattern, so it works much like =. Capital letters may still be handled differently, depending on the database.
