RegEx: Change a number in a table string

[bertbierhuis]

Hello,

 

I have a newbe question: I want to add the amount of 1500 to a number that has to be found in a string. Through out an entire table of a MySQL database, with a query with a regular expression.

 

So e.g.:

 

[Lorem ipsum=88 dolor]

 

would become:

 

[Lorem ipsum=1588 dolor]

 

Can any one help me with this?

 

Kind regards, Bert

[mac_gyver]

there is no 'regex replace' in mysql. the regex pattern match would only let you find the rows containing the data that you need to update.

 

to do this in a query would require that you use string functions to find the position of the start/end of the number in the string, split the string to get the number part, add the amount to the number, then replace the value in the string.

 

you would be better off retrieving the data using php and use preg_replace to modify the data.

 

if this is a regular occurrence, i.e. you set/update the value regularly, the value should be stored separate from the string it is in.

[benanamen]

 the value should be stored separate from the string it is in.

 

That is really the start of your answer. You are storing your data incorrectly which will force you to continually create hacks to manage your data. Look up and learn "Database Normalization".

Edited August 22, 2016 by benanamen

[Barand]

Not pretty, but it works (I think). You can use sub-elements of this solution to break down that single column into three columns in your redesigned table, viz.

• prestring
• quantity
• poststring
•  

Data

mysql> select * from tablea;
+----+-------------------------------+
| id | col_a                         |
+----+-------------------------------+
|  1 | Lorem ipsum = 88 dolor        |
|  2 | De profundis = 102 clamavi    |
|  3 | matutina usque ad = 55 noctem |
+----+-------------------------------+

Query and results:

UPDATE tablea
SET col_a =  CONCAT (
      SUBSTRING_INDEX(col_a, '=', 1)
    , ' = '
    , SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(col_a, '=', -1)), ' ', 1) + 1500
    , ' '
    , SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(col_a, '=', -1)), ' ', -1)
    )
;

mysql> select * from tablea;
+----+----------------------------------+
| id | col_a                            |
+----+----------------------------------+
|  1 | Lorem ipsum  = 1588 dolor        |
|  2 | De profundis  = 1602 clamavi     |
|  3 | matutina usque ad  = 1555 noctem |
+----+----------------------------------+
3 rows in set (0.00 sec)