从记录中删除 HTML 标签-数据库问题

Remove HTML tags from record(从记录中删除 HTML 标签)

本文介绍了从记录中删除 HTML 标签的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

需要帮助来形成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 ="bXl0ZXh0LmpwZw=="> 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>');


                        
这篇关于从记录中删除 HTML 标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！