Replace part of a string with another string, not always being the same initial string?

Question

I have the following table (I know this rows separated by comma are wrong, but I'm just doing the job):

Table fruits:

And I need to make a query to delete all pears, so as there are separated by comma values, I actually need an UPDATE query.

So I'm trying with REPLACE like this:

UPDATE fruits SET fruit_data=REPLACE(fruit_data,',greenPear','');    --that comma needs to
UPDATE fruits SET fruit_data=REPLACE(fruit_data,'greenPear,','');    --be deleted too
UPDATE fruits SET fruit_data=REPLACE(fruit_data,'greenPear','');
DELETE FROM fruits WHERE fruit_data = '';

And that works, but just for "green pears" and I need to delete "all" pears, not just the green ones. There are twenty different colors and I need to know if there are a way to not repeat twenty times the above code.

You can probably do this in MySQL 8.0, which I believe has extended preg_match capabilities. Otherwise, you're stuck. You could more usefully spend the time fixing the database. — Strawberry
– Strawberry, Commented May 21, 2018 at 11:04
Can you provide the output of "SHOW CREATE TABLE fruits", or do you have any primary key columns in table? — user8406805
– user8406805, Commented May 21, 2018 at 11:12
@KKK yes, the table has his primary key. Actually that fruits table is just illustrative, the original table has values with more complex data. I've eddited the image. — Roberto Sepúlveda Bravo
– Roberto Sepúlveda Bravo, Commented May 21, 2018 at 11:21
I have added answer below you may check out, you will be out of stuck for now. — user8406805
– user8406805, Commented May 21, 2018 at 11:45

user8406805 · Accepted Answer · 2018-05-21 11:30:05Z

1

If you have the primary key in table then the below SQL will really helpful to you:

Your SQL should like this as per my best tries:

update fruits f
left join (
select id,group_concat(fruit) new_fruit_data from(
SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1) AS fruit
,count(1)
FROM (
SELECT id,fruit_data FROM fruits) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10  cifre, b.i + a.i * 10  sute
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
) x
ON (length(fruit_data)-length(replace(fruit_data,',',''))+1) >= x.cifre
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1)) k
where fruit not rlike 'pear'
group by id) n
on f.id = n.id
set f.fruit_data = n.new_fruit_data;

Explaination in Details:

Setting up table:

create table fruits(id int,fruit_data varchar(500));
insert into fruits values(1,'greenApple,greePear,redApple');
insert into fruits values(2,'greePear');
insert into fruits values(3,'redApple,orangePear');
insert into fruits values(4,'greenApple,redApple');
insert into fruits values(5,'yellowPear,greenApple,greenPear');

Your base table data.

mysql> select * from fruits;
+------+---------------------------------+
| id   | fruit_data                      |
+------+---------------------------------+
|    1 | greenApple,greePear,redApple    |
|    2 | greePear                        |
|    3 | redApple,orangePear             |
|    4 | greenApple,redApple             |
|    5 | yellowPear,greenApple,greenPear |
+------+---------------------------------+
5 rows in set (0.00 sec)

Here is the workaround and your solution:

mysql> update fruits f
    -> join (
    -> select id,group_concat(fruit) new_fruit_data from(
    -> SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1) AS fruit
    -> ,count(1)
    -> FROM (
    -> SELECT id,fruit_data FROM fruits) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10  cifre, b.i + a.i * 10  sute
    ->     FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    ->     CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    -> ) x
    -> ON (length(fruit_data)-length(replace(fruit_data,',',''))+1) >= x.cifre
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.fruit_data, ',', x.cifre), ',', -1)) k
    -> where fruit not rlike 'pear'
    -> group by id) n
    -> on f.id = n.id
    -> set f.fruit_data = n.new_fruit_data;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 4  Changed: 3  Warnings: 0

Output, after update:

mysql> select * from fruits;
+------+---------------------+
| id   | fruit_data          |
+------+---------------------+
|    1 | greenApple,redApple |
|    2 | NULL                |
|    3 | redApple            |
|    4 | greenApple,redApple |
|    5 | greenApple          |
+------+---------------------+
5 rows in set (0.00 sec)

answered May 21, 2018 at 11:30

user8406805

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

8 Comments

user8406805 Over a year ago

Here I have created a column called ID for unique values in table, replace ID column with your primary key column name

user8406805 Over a year ago

I have added the "LEFT" with "JOIN" so that ID-2 will be updated with null

Roberto Sepúlveda Bravo Over a year ago

this almost works! I just need to remove that NULL column but the DELETE FROM fruits WHERE fruit_data = NULL doesn't works :(

user8406805 Over a year ago

delete from fruits where fruit_data is null;

user8406805 Over a year ago

No one is God, next time you will be God as now you familiar with this complex problem. that experience only. :)

|

Collectives™ on Stack Overflow

Replace part of a string with another string, not always being the same initial string?

1 Answer 1

Explaination in Details:

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explaination in Details:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related