Geek asks and answers

MySql - adding a row to a specific position in the list

If I have a column in my table that is a list of rows, how do I add a new record to the list in the table (for all rows) after a specific item in the list?

for example: 
apple, orange, peach, banana,....

So, let's say I want to add lemonafter writing peachto each line. I know that the peach entry appears on each line, and this happens only once (the position of the peach in the list may vary, and the appearance of other elements in the list may change). Thanks in advance!

I tried (but did not work)

UPDATE TABLE_NAME 
concat (COLUMN_NAME, 'lemon') 
where COLUMN_NAME like '%peach,%';
+3
source share
4 answers
UPDATE TABLE_NAME 
set COLUMN_NAME= replace(COLUMN_NAME, 'peach','peach,lemon') 
where COLUMN_NAME like '%peach,%';
+5
source

You can try as below

create table test(test VARCHAR(255));
Query OK, 0 rows affected (0.26 sec)

INSERT INTO test VALUES('apple, orange, peach, banana,....');
Query OK, 1 row affected (0.00 sec)

SELECT * FROM test;
+-----------------------------------+
| test                              |
+-----------------------------------+
| apple, orange, peach, banana,.... |
+-----------------------------------+
1 row in set (0.00 sec)

UPDATE test SET test = REPLACE(test,'peach,','peach,lemon,') WHERE test like '%peach,%';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM test;
+-----------------------------------------+
| test                                    |
+-----------------------------------------+
| apple, orange, peach,lemon, banana,.... |
+-----------------------------------------+
1 row in set (0.00 sec)
+1
source

If the string is 'peach'not displayed as part of any other value in the “list” (that is, for example, there are no occurrences of terms in the list, for example, 'impeach'or 'peachtree', then you can use the string function REPLACEto find the occurrences 'peach'and replace it with'peach,lemon'

For instance:

UPDATE mytable
   SET mycol = REPLACE(mycol, 'peach', 'peach,lemon')

In a more general case, you may not have a guarantee that 'peach'it is not part of any other term in the list, the expression for this will be more complex.

+1
source 
update tblname
set clm_name= replace(clm_name,'peach','peach,lemon') 
where clm_name like '%peach%';
+1
source

More articles:


All Articles