我需要删除一个表中的所有链接表单列。对于像这样的列条目:
American drama film directed by <a href="http://www.google.com">Richard LaGravenese</a>
我需要删除整个链接,所以它会像这样结束:
American drama film directed by Richard LaGravenese
是否有办法做到这一点与一个单一的UPDATE语句?(mysql)
你可以运行一个正则表达式
尝试使用mysql-udf-regexp
看看这篇文章,它可能会很有用
如何在MySQL中做正则表达式替换?
可以创建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('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.');
+----------------------------------------------------------------------+
| strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.') |
+----------------------------------------------------------------------+
| hello world again. |
+----------------------------------------------------------------------+
1 row in set
Thanks to the post https://stackoverflow.com/a/13347316/2689199