mysql update REPLACE with concat

Question

my table save same ID like that : 123,9901,888,99

I need change some id in this field

I using my sql : let 9901 change to 2001

UPDATE `TALBE_` SET id_group = REPLACE (id_group, '9901', '2001');

That's work

but, I change 99 to 100

UPDATE `TALBE_` SET id_group = REPLACE (id_group, '99', '100');

My sql fidle had changed to

123,10001,888,100

how to just change 99 -> 100, dont change 9901?

Do I need use concat?

But I test some time still cant do that

Never store multiple values in a single field. Changing your table design is the only real solution to this problem — juergen d
– juergen d, Commented Jun 7, 2016 at 10:56
Thank you for your reminder but, the urgent need to use this sql I need to modify the system occurred — Sky
– Sky, Commented Jun 7, 2016 at 11:14

Solarflare · Accepted Answer · 2016-06-07 11:24:01Z

4

You can use e.g.

UPDATE `TALBE_` SET id_group = 
    trim(',' from REPLACE(concat(',', id_group, ','), ',99,', ',100,'));

answered Jun 7, 2016 at 11:24

Solarflare

11.2k2 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sky Over a year ago

Thank you, thanks for you let me know I can use trim()

Bernd Buffen · Accepted Answer · 2016-06-07 11:54:03Z

her a version that seeks the number a replace it

You must insert the search value 2 times

SELECT CONCAT_WS (',',
      SUBSTRING_INDEX(id_group, ',', FIND_IN_SET('888',id_group)-1)
      ,'100',
      SUBSTRING_INDEX(id_group, ',', --1* (LENGTH(REGEXP_REPLACE(id_group,'[0-9]','')) -(FIND_IN_SET('888',id_group)-1)))
      );

Sample

MariaDB [(none)]> SELECT CONCAT_WS (',',
    ->       SUBSTRING_INDEX('123,9901,888,99', ',', FIND_IN_SET('888','123,9901,888,99')-1)
    ->       ,'100',
    ->       SUBSTRING_INDEX('123,9901,888,99', ',', --1* (LENGTH(REGEXP_REPLACE('123,9901,888,99','[0-9]','')) -(FIND_IN_SET('888','123,9901,888,99')-1)))
    ->       );
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT_WS (',',
      SUBSTRING_INDEX('123,9901,888,99', ',', FIND_IN_SET('888','123,9901,888,99')-1)
      ,'100',
      SUBSTRING_INDEX('123,9901,888,99', ',', --1* (LENGTH(REGEXP_REPLACE('123,9901,888,99','[0-9]','')) -(FIND_IN_SET('888','123,9901,888,9 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 123,9901,100,123                                                                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Collectives™ on Stack Overflow

mysql update REPLACE with concat

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related