MySQL正则表达式搜索和替换[重复]

This question already has an answer here:

这个问题在这里已有答案：

How to do a regular expression replace in MySQL? 10 answers
如何在MySQL中替换正则表达式？ 10个答案

I would like to remove height property from all my images in my database. Their markup is as follows:

我想从我的数据库中的所有图像中删除height属性。他们的加价如下：

<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查询中使用正则表达式。我确实发现它们存在，我只是不知道如何在这种情况下使用它们。

1 个解决方案

#1

Source

资源

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);

#1