MySQL Regex 搜索和替换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22421840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Regex Search and replace
提问by Jinx
I would like to remove height property from all my images in my database. Their markup is as follows:
我想从我的数据库中的所有图像中删除高度属性。它们的标记如下：
<img src="path/img.jpg" width="x" height="y" />
I was going to do something like this:
我打算做这样的事情：
UPDATE jos_content SET introtext = REPLACE(introtext, 'height=".*"', '');
But I don't know how to use regular expressions in MySQL query. I did find out they exist, I just don't se how I can use them in this context.
但我不知道如何在 MySQL 查询中使用正则表达式。我确实发现它们存在，我只是不知道如何在这种情况下使用它们。
回答by Vignesh Kumar A
Try Below
试试下面
DELIMITER $$
CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000))
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern THEN
loop_label: LOOP
IF i>CHAR_LENGTH(original) THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original,i,1);
IF NOT ch REGEXP pattern THEN
SET temp = CONCAT(temp,ch);
ELSE
SET temp = CONCAT(temp,replacement);
END IF;
SET i=i+1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END$$
DELIMITER ;
And Run your Update query as
并运行您的更新查询
UPDATE jos_content SET introtext = regex_replace('height=".*"', 'height=""',introtext);
