从记录中删除HTML标签

Question

需要帮助从具有以下内容的表一列构成MYSQL查询

Row1 : this is first <a href='mytext.txt'>row</a> from the table

Row 2 : THis is the second row <img src ='mytext.jpg'> my image is there

Row 3 : <p>This is the Third row  my mytext is there  </p>

Row 4 : <p class='te_mytext'>This is the Third row  my text is there  </p>

这是表格行，我尝试将关键字搜索为“ mytext”

我的查询是

SELECT * from table  WHERE colmn_name ` like '%mytext%' "

我将得到所有4行作为结果，但结果是错误的。我只需要第3行就可以得到正确的输出。之所以只在内容中包含mytext的该行不在内容中，而是在所有行中都具有mytext的原因，

如何编写MySQL查询？

Answer 1

这是我对strip_tags函数的实现：CREATE FUNCTION `strip_tags`($str text) RETURNS textBEGIN&nbsp; &nbsp; DECLARE $start, $end INT DEFAULT 1;&nbsp; &nbsp; LOOP&nbsp; &nbsp; &nbsp; &nbsp; SET $start = LOCATE("<", $str, $start);&nbsp; &nbsp; &nbsp; &nbsp; IF (!$start) THEN RETURN $str; END IF;&nbsp; &nbsp; &nbsp; &nbsp; SET $end = LOCATE(">", $str, $start);&nbsp; &nbsp; &nbsp; &nbsp; IF (!$end) THEN SET $end = $start; END IF;&nbsp; &nbsp; &nbsp; &nbsp; SET $str = INSERT($str, $start, $end - $start + 1, "");&nbsp; &nbsp; END LOOP;END;我确保它删除了不匹配的左括号，因为它们很危险，尽管它忽略了任何未配对的右括号，因为它们无害。mysql> select strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.');+----------------------------------------------------------------------+| strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.') |+----------------------------------------------------------------------+| hello world again.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|+----------------------------------------------------------------------+1 row in set

Answer 2

这些行添加到fnStripTags功能后SET Dirty = Insert( Dirty, iStart, iLength, '');set Dirty = Replace(Dirty,'&nbsp;',''); #No space between & and nbsp;set Dirty = Replace(Dirty,'\r','');set Dirty = Replace(Dirty,'\n','');