从记录中删除HTML标记

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

Row1 : this is first row from the table

Row 2 : THis is the second row my image is there

Row 3 :

This is the Third row my mytext is there

Row 4 :

This is the Third row my text is there

这是我尝试将关键字搜索为'mytext'的表格行

我的疑问是

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

我会得到所有4行,但结果是错误的.我需要得到正确的输出只有第3行.这一行只有内容中的mytext所有其他内容的原因不在于内容,而是mytext在所有行中

如何编写MySQL查询？

尝试这个解决方案:不是自己尝试,但显然它有效.

来源:http://forums.mysql.com/read.php？52,177343,177985 #msg-177985

   SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC 
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;
|
DELIMITER ;
SELECT fnStripTags('this is a test, nothing more');

这是我对strip_tags函数的实现:

CREATE FUNCTION `strip_tags`($str text) RETURNS text
BEGIN
    DECLARE $start, $end INT DEFAULT 1;
    LOOP
        SET $start = LOCATE("<", $str, $start);
        IF (!$start) THEN RETURN $str; END IF;
        SET $end = LOCATE(">", $str, $start);
        IF (!$end) THEN SET $end = $start; END IF;
        SET $str = INSERT($str, $start, $end - $start + 1, "");
    END LOOP;
END;

我确保它删除了不匹配的开括号,因为它们很危险,但它忽略了任何不成对的右括号,因为它们是无害的.

mysql> select strip_tags('hello wo<>rld <again<.');
+----------------------------------------------------------------------+
| strip_tags('hello wo<>rld <again<.') |
+----------------------------------------------------------------------+
| hello world again.                                                   |
+----------------------------------------------------------------------+
1 row in set

SELECT * from table  WHERE colmn_name REGEXP  '>[^<]*mytext';