bertbierhuis Posted August 22, 2016 Share Posted August 22, 2016 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted August 22, 2016 Share Posted August 22, 2016 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. Quote Link to comment Share on other sites More sharing options...
benanamen Posted August 22, 2016 Share Posted August 22, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 22, 2016 Share Posted August 22, 2016 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) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.