Search code examples
mysqlsql-like

How to use a percent (%) in a LIKE without it being treated as a wildcard?

I have a database that is supposed to contain all top-level and second-level domain names. But the feed I'm parsing contains a lot of sub folders, I'd like to delete any row that contains any % sign in it, but I am having a hard time figuring out how to use a percent sign as the field I'd like to match, while still using the LIKE feature. Below is the code I'm trying to use:

select FROM `001ProductList` WHERE programURL  LIKE '%%%'

Here is an example of what I'm trying to match:

www.site.com%3Ack-5941560-10463497?url=http%3A%2F%2Fwww.example.com%2Fproddetail.aspx%...

If I encounter a row with a % sign in it, I want to delete it.

Solution

  • Escape the literal % character:

    select * 
FROM 001ProductList
WHERE programURL LIKE '%\%%'

    or use regex

    WHERE programURL RLIKE '%'