从记录中删除 HTML 标签 [英] Remove HTML tags from record
问题描述
需要帮助来形成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>');
这篇关于从记录中删除 HTML 标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
