MySQL LIKE: Search for a keyword present into a text but not into html tags?

user_2772127 0
Viewed 27

I want to fetch records in which a given keyword is present into the text but not present within any html tag.
Example with the keyword "span":

Hello <span>world</span>

No, the keyword is only present into tags.

Hello <span>world</span>, blah blah span blah blah

Ok, the keyword is present in the text.

This is what I have so far:

SELECT * FROM mytable WHERE `content` LIKE '%span%' AND `content` NOT LIKE '<%span%>';

But it doesn't work.
What is the right way to do this?

mysql
1 Answers

SQL's LIKE operator is truly not suitable for searching in a way that requires dealing with HTML tags. Whatever you do here will be brittle (prone to delivering bogus results).

That being said, try this.

SELECT * FROM mytable
 WHERE content LIKE '%span%'
   AND content NOT LIKE '%<span>%'   /* opening tag */
   AND content NOT LIKE '%</span>%'; /* closing tag */

As long as the <span> tags in your HTML are simple this might get you the results you want. But as soon as you have <span id="spanid" class="text"> for a span tag, you can't use LIKE any more. The second % in LIKE '%<span%>%' matches everything in your HTML from the first <span...> up to the last closing tag.

You might consider using RLIKE and a regular expression. But those too are brittle.

user_2056080
Related