CODEWITHSUNDEEP
mysql
Crys
Crys

Reputation: 67

Replace bulk values in a field - MySql

I have some html code saved in field "meta_value" from table "wp_postmeta" that looks like: <img src="...." height="X" width="Y" > I want to replace X and Y from all fields with "100%" but is this possible in MySql? X and Y are different values in each record, like X=200, Y=350 - first record, X=325, Y=200 in second record and so on.

I saw the Mysql search and replace some text in a field question but can i use a similar solution to update bulk values for height="X" and width="Y"?

Thank you!

Upvotes: 0

Views: 174

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

This is complicated and lets start with example how we can do it with mysql.

mysql> create table test (post text);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test values ('<img src="...." height="100" width="200">'),('<img src="...." height="300" width="400" >');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test ;
+--------------------------------------------+
| post                                       |
+--------------------------------------------+
| <img src="...." height="100" width="200">  |
| <img src="...." height="300" width="400" > |
+--------------------------------------------+

Now lets use some functions to do the replace

select 
replace
(
  post,
  concat(
    'height="',
    substring_index(
     substring_index(
      post,'height="',-1
     ),
     '"',1
    ),
    '"'
   ),
   concat(
    concat(
     'height="',substring_index(
       substring_index(
        post,'height="',-1
       ),
      '"',1
     ),
    '%"'
   )
  )
) as height from test;

+---------------------------------------------+
| height                                      |
+---------------------------------------------+
| <img src="...." height="100%" width="200">  |
| <img src="...." height="300%" width="400" > |
+---------------------------------------------+
2 rows in set (0.00 sec)

Similarly the width could be replaced as

select 
replace
(
  post,
  concat(
    'width="',
    substring_index(
     substring_index(
      post,'width="',-1
     ),
     '"',1
    ),
    '"'
   ),
   concat(
    concat(
     'width="',substring_index(
       substring_index(
        post,'width="',-1
       ),
      '"',1
     ),
    '%"'
   )
  )
) as width from test;

+---------------------------------------------+
| width                                       |
+---------------------------------------------+
| <img src="...." height="100" width="200%">  |
| <img src="...." height="300" width="400%" > |
+---------------------------------------------+
2 rows in set (0.01 sec)

Finally using them in the update command as

update test set
text =  
replace
(
  post,
  concat(
    'height="',
    substring_index(
     substring_index(
      post,'height="',-1
     ),
     '"',1
    ),
    '"'
   ),
   concat(
    concat(
     'height="',substring_index(
       substring_index(
        post,'height="',-1
       ),
      '"',1
     ),
    '%"'
   )
  )
),
text = 
replace
(
  post,
  concat(
    'width="',
    substring_index(
     substring_index(
      post,'width="',-1
     ),
     '"',1
    ),
    '"'
   ),
   concat(
    concat(
     'width="',substring_index(
       substring_index(
        post,'width="',-1
       ),
      '"',1
     ),
    '%"'
   )
  )
) ;


mysql> select * from test ;
+----------------------------------------------+
| post                                         |
+----------------------------------------------+
| <img src="...." height="100%" width="200%">  |
| <img src="...." height="300%" width="400%" > |
+----------------------------------------------+
2 rows in set (0.00 sec)

Upvotes: 1

cSteusloff
cSteusloff

Reputation: 2628

By default, mysql has not a function like MariaDB:

REGEXP_REPLACE(subject, pattern, replace)

You can create your one function, shown in this blog: https://techras.wordpress.com/2011/06/02/regex-replace-for-mysql/

Upvotes: 0

Related Questions