Nrthugu

for example, consider a table like below (table name=accounts)

account        owner_ids

PA account     sa123,rt458,hf678,de348f

RA account     gg678n,fk980,rt458,hf678

Here, I want to remove owner_ids "rt458" and "hf678" from the entire table and after removing the commas should be perfect (I mean after removing also the remaining owner_ids should be separated by comma as before and there should not be any unnecessary comma in the column)

Any kind of help is appreciated, Help me out guys

You can use REPLACE function twice:

update accounts 

set owner_ids = REPLACE(REPLACE(owner_ids,

             ',rt458' ),',hf678');

REPLACE returns char with every occurrence of search_string replaced with replacement_string.

Or you can use REGEXP_REPLACE to replace/remove those values by regex:

update accounts 

set owner_ids = REGEXP_REPLACE(owner_ids,

             '(,rt458||,hf678)', '');

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern.

To support case where rt458 is the first entry in the CSV list (As @TimBiegeleisen commented) you will have to allow no comma as:

update accounts 

set owner_ids = REGEXP_REPLACE(owner_ids,

             '(,{0,1}rt458||,{0,1}hf678)', '');

Using replace you will need to double calls:

update accounts 

set owner_ids = REPLACE(REPLACE(REPLACE(REPLACE(owner_ids,

             ',rt458' ),',hf678', 'rt458,', 'hf678,');

Also you can see other solutions to remove value from comma separated string

UPDATE accounts

SET owner_ids = TRIM(BOTH ',' FROM

    REPLACE(REPLACE(CONCAT(',', owner_ids, ','), ',rt458,', ''), ',hf678,', '')

);

First prepend and append the field with a comma CONCAT(',', owner_ids, ',') to make it uniform.

Then, remove ,rt458, and ,hf678, from it.

Later remove commas from both at the start and end which we added to make the structure uniform.

This will maintain single commas between the values.

Please note, you should prefer normalizing owner_ids so that such updates are easy to handle.