SQL 'LIKE' query using '%' where the search criteria contains '%'

When you're using SQL's LIKE operator and your search term includes the % character, it can get a bit tricky because % is a wildcard in SQL. It matches any number of characters, so if you're trying to find a literal % in your data (like a discount field with values such as 10%), you’ll need to escape the % character.

Here's how you can handle it:

Use an escape character: SQL allows you to define a custom escape character so that special characters like % or _ can be treated as literals.

Syntax example:

SELECT * FROM products

WHERE discount LIKE '%%%' ESCAPE '';

 In this query:

• % tells SQL to treat % as a normal character.
  
• ESCAPE '' tells SQL that the backslash () is being used as the escape character.
  

What if you’re using underscores (_)?

•  Similar to %, the underscore is a single-character wildcard. Escape it the same way using your chosen escape character.
  

Quick Tips:

• Always test your pattern on a few records before applying it broadly.
  
• This approach is widely supported in SQL Server, MySQL, and PostgreSQL, but syntax may vary slightly.
  

Using escape characters ensures that SQL interprets your intent correctly and doesn't treat wildcards as actual wildcards when you don't want it to.