从记录中删除HTML标签 [英] Remove HTML tags from record
问题描述
需要帮助来形成MYSQL查询 来自表中具有波纹管内容的一列
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>
这是我尝试将关键字搜索为"mytext"的表行
this is the table rows i try to search the keyword as 'mytext'
我的查询是
SELECT * from table WHERE colmn_name ` like '%mytext%' "
我将得到所有4行，但结果是错误的.我只需要第3行就可以得到正确的输出.之所以仅在内容中包含mytext的该行不在内容中，而是在所有行中都包含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
如何编写MySQL查询?
How can I write the MySQL query?
推荐答案
尝试以下解决方案:我自己没有尝试过，但显然可以使用.
try this solution: not tried it myself but apparently it works.
源: 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屋！
