从记录中删除 HTML 标签

2021-11-20 00:00:00 mysql

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

Row1 : 这是第一个 

 SET GLOBAL log_bin_trust_function_creators=1;如果存在 fnStripTags，则删除函数；分隔符 |创建函数 fnStripTags( Dirty varchar(4000) )返回 varchar(4000)确定性开始声明 iStart, iEnd, iLength int;WHILE Locate('<', Dirty) >0 And Locate('>', Dirty, Locate('<', Dirty)) >0 做开始SET iStart = Locate('<', Dirty), iEnd = Locate('>', Dirty, Locate('<', Dirty));SET iLength = ( iEnd - iStart) + 1;如果 iLength >0 那么开始SET Dirty = Insert( Dirty, iStart, iLength, '');结尾;万一;结尾;结束时；返回脏；结尾;|分隔符；SELECT fnStripTags('this <html>is <b>a test</b>，仅此而已</html>');

Need help to form the MYSQL query from table one column having the bellow content

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>

this is the table rows i try to search the keyword as 'mytext'

my query is 

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

I will get all the 4 rows as result but the result is wrong. I need to get the correct output as only Row 3. The reason this row only having the mytext inside the content all other are not in content but mytext having in all rows

How can I write the MySQL query?

解决方案

try this solution: not tried it myself but apparently it works.

source: 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 <html>is <b>a test</b>, nothing more</html>');

相关文章