MySQL Regex Search and replace [duplicate]

Question

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.

Vignesh Kumar A · Accepted Answer · 2014-03-15 09:23:48Z

8

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

answered Mar 15, 2014 at 9:23

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sharky Over a year ago

why REPLACE( and not regex_replace( ?

Vignesh Kumar A Over a year ago

@Sharky I have made change...

Jinx Over a year ago

It worked! But what a behemoth of a function. I thought using regex in mysql was as easy as 123.

Kristen Grote Over a year ago

I'm a little confused about how this works. The way I'm reading it, this loops through one character at a time and checks the regex against each individual character. So it checks to see if the letter 'h' matches the regex of height=".*", which of course it doesn't - so how could this work? I must be missing something.

kannetkeifer Over a year ago

this not work exactly as regex replace because the amount of characters matching can be different there is no logic doing that in this code. when i tested i specifically added ^ to match only beginning of the string but it still replace the numbers at the end too.

Collectives™ on Stack Overflow

MySQL Regex Search and replace [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related