13

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.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Robert82
  • 370
  • 2
  • 4
  • 15
  • http://stackoverflow.com/questions/15214509/what-does-backslash-mean-in-an-sql-query/15215106#15215106 , http://stackoverflow.com/questions/10803489/sql-like-query-using-where-search-criteria-contains – user2864740 Nov 03 '13 at 04:26

2 Answers2

26

Escape the literal % character:

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

or use regex

WHERE programURL RLIKE '%'
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like – Déjà vu Nov 03 '13 at 04:26
  • @Bohemian `SELECT * FROM `001ProductList` WHERE programURL LIKE '%/%%' LIMIT 0 , 30` selects everything in the table, including urls that don't contain percent signs. – Robert82 Nov 03 '13 at 04:31
  • 3
    That's a *backslash* before the middle percent in the first version (your comment has a *forward* slash, which won't work), but I would use the second version too - easier to read because percent is not a special regex character (doesn't need escaping) and you need only code the percent because `RLIKE` only needs to *partially* match – Bohemian Nov 03 '13 at 04:39
  • 1
    You're right on the first one, my mistake. Thank you greatly for the help mate! – Robert82 Nov 03 '13 at 04:47
3

You may use the LOCATE function, like so:

SELECT * 
FROM `001ProductList`
WHERE LOCATE('%', `programURL`) <> 0;
aefxx
  • 24,835
  • 6
  • 45
  • 55
Nik Terentyev
  • 2,270
  • 3
  • 16
  • 23