Remove item from list in MySQL database

Question

I've a MySQL database in which there is a table named customers. Inside this table there is a field named preferences, which content is a list of names, single or composite.

Table customers:

id   name   preferences
---------------------------------
1    John   pears,tomato sauce,bread
2    Rose   carrot cake, salad

I can easily add an new entry in the field preferences doing something like:

UPDATE `customers` 
  SET preferences = CONCAT(preferences,',oranges') 
WHERE id=2

But now, I wonder if there is an easy way to remove a known item in that list, which does not mean, read back the registry entry, processing it using PHP and saving it again.

I was trying to use REGEX_REPLACE unsuccessfully, since the word can be at the beginning, middle or end. So I didn't succeed to write a proper expression for substituting the results. Anyway Regular expressions, is probably not the right way to go.

Thanks

You may want to read up on [normal form](https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization). It will cause you a lot less headaches down the stretch. — esqew, Jun 11 '18 at 13:34
Yes, the list is strictly comma separated: Comma won't be used in the words — Carles, Jun 11 '18 at 13:34
Could not post it as a "answer" because the question was marked as duplicate before i could post but take a look to this https://www.db-fiddle.com/f/21o7s3GeerqY4ifWEz1G9n/3 if you can't normalize.. Normalizing also would require the application code to be changed. — Raymond Nijland, Jun 11 '18 at 14:01

Andrei Suvorkov · Answer 1 · 2018-06-12T12:33:24.263

1

Try this:

Update `customers`
    set preferences = TRIM(BOTH ',' FROM TRIM(BOTH 'oranges' FROM TRIM(REPLACE(CONCAT(" "',oranges'" "),' ,oranges ',''))));

It will delete ',oranges'. If there is no space before and after ,oranges it would not delete it. As you can see it is not a Regex anymore. Also TRIM(BOTH 'oranges' will delete 'oranges' from first and last place in the string.

DEMO

edited Jun 12 '18 at 12:33

answered Jun 11 '18 at 13:30

Andrei Suvorkov

5,559
5
22
48

I know, but this is not valid, since there could be composite words like: ",oranges (pre-cutted)", so you would be deleting half of the term. – Carles Jun 11 '18 at 13:32
1

I have edited my answer, please have a look – Andrei Suvorkov Jun 11 '18 at 13:35
3

@Carles - that is the **exact** reason why you should normalize your DB. Split up your DB into 3 tables: `customers`, `preferences`, `customer_preferences` and then link them via foreign keys. It sounds difficult but in reality, it is simple as pie. – waterloomatt Jun 11 '18 at 13:35
1

Thanks @AndreiSuvorkov, I will try that, but anyway I think the right way to go is normalization. – Carles Jun 11 '18 at 13:38
MySQL REGEXP doesn't have the lookahead nor lookbehind features. – Casimir et Hippolyte Jun 11 '18 at 13:52
@CasimiretHippolyte good point, anyway Carles will do normalization, which is in this case more preferable – Andrei Suvorkov Jun 11 '18 at 13:59
@AndreiSuvorkov - Sorry, but I have to down vote this because it cannot work. MySQL's `REPLACE` does not work with regular expressions. – Rick James Jun 12 '18 at 03:18
@RickJames thank you for your feedback, I have edited my answer, and I think found a good solution for it – Andrei Suvorkov Jun 12 '18 at 04:32
@AndreiSuvorkov - That's better. Now what about `oranges` being the first or last in the commalist? – Rick James Jun 12 '18 at 04:39
@RickJames I think now it works perfect. Please have a look. – Andrei Suvorkov Jun 12 '18 at 05:18
@Carles I have edited my answer and I think it is good solution now, probably you can use it as well. Please have a look on it. – Andrei Suvorkov Jun 12 '18 at 05:18

Remove item from list in MySQL database

1 Answers1