Jump to content

RegEx: Change a number in a table string


bertbierhuis

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 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 by benanamen
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.