How to remove value from comma separated string using update query?

Question

Recruiter list,Employer List,Latest Jobs,Intrested Recruiter,Recruiter Resume,Shortlisted Hr Resume

UPDATE assign SET menu=REPLACE(REPLACE(CONCAT(',', menu, ','), 'Shortlisted Hr Resume', ''), ',,', ',') WHERE hr_id='hr2911025540'

In this case, I have a string Recruiter List, Shortlisted Hr Resume, Add Hr. Now I want to remove only Shortlisted Hr Resume but this query replace value from both ending and output look like ,Employer List,Latest Jobs,Intrested Recruiter,Recruiter Resume, So, How can I do this please help me ?

The best solution is to seperate and normalize these values. Having such a cumulated list of values always opens a ton of problems — Nico Haase, Dec 18 '18 at 09:51
Please see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad on why you shouldn't use comma separated values in SQL. — Progman, Dec 18 '18 at 09:56

score 0 · Answer 1 · answered Dec 18 '18 at 09:59

UPDATE groups
SET
  description =
    TRIM(BOTH ',' FROM REPLACE(CONCAT(',', description, ','), ', Shortlisted Hr Resume,', ','))
  WHERE id='1'

You can also

UPDATE yourtable
SET
  description =
    TRIM(BOTH ',' FROM
      REPLACE(
        REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
    )
WHERE
  FIND_IN_SET('2', description)

How to remove value from comma separated string using update query?

1 Answers1