MySQL update part of a whole string?

Question

I store numeric variables in a string. Like:165,37,0,0,21

Now i have to change only the last part but keep the rest the same. (in my example i need to change 21 to 0)

Do you have any ideas?

Don't store values as a list in a string, store them individually in a separate table — Joakim Danielson, Dec 06 '18 at 09:45
https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — vaso123, Dec 06 '18 at 09:50
Is it always only the last part that changes? Or do you need to change some inner values too from time to time? If the later also occurs, normalize your data. — Nico Haase, Dec 06 '18 at 10:02
@NicoHaase yes, i have to change only the last part, in every rows. — Alfaro Christian, Dec 06 '18 at 10:05

score 0 · Accepted Answer · answered Dec 06 '18 at 10:27

0

I sugest using regular expression for this, the below reg ex will find the number at the end of the string, including the last comma, and replace it with ',0'

UPDATE table SET column1 = REGEXP_REPLACE(column1 , ',[0-9]*$', ',0')

answered Dec 06 '18 at 10:27

Joakim Danielson

43,251
5
22
52

score -1 · Answer 2 · answered Dec 06 '18 at 09:59

-1

REPLACE would be most ideal in this case:

UPDATE tbl_name 
SET 
field_name = REPLACE(field_name,
    string_to_find,  -- in your case 21
    string_to_replace  -- in your case 0 ) 
WHERE
<place condition if any e.g. the key to that record>;

answered Dec 06 '18 at 09:59

Code Chef

82
3

This will not work if there are multiple 21's or numbers containing 21 like 211. – Joakim Danielson Dec 06 '18 at 10:17

MySQL update part of a whole string?

2 Answers2