-1

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

krithikaGopalakrishnan
  • 1,217
  • 10
  • 28
Vedha
  • 43
  • 2
  • 8
  • Helping you out here means pointing out that your CSV data is unnormalized, and therefore hard to work with. You should get owner id onto a separate row. – Tim Biegeleisen Nov 11 '18 at 07:33
  • @vega et al. - I see you marked this as a duplicate of another question. What you did here is an abuse; the question you linked to is different. You may have your own opinion on how bad the OP's data model is (I agree with that opinion), but that doesn't give you the right to abuse the system. Shame on you. –  Nov 11 '18 at 14:31

2 Answers2

1

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

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • 1
    What about the case where `rt458` is the first entry in the CSV list? – Tim Biegeleisen Nov 11 '18 at 07:32
  • @TimBiegeleisen Thanks for commenting, I updated my answer – Ori Marko Nov 11 '18 at 07:45
  • What about the case where there is another token, `rt4586`, which mustn't be removed? You will just remove `rt458` from it, leaving the `6`. The problem can be solved with REPLACE, but the solution must be written with much more care than you have. –  Nov 11 '18 at 15:46
0
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.

Samir Selia
  • 7,007
  • 2
  • 11
  • 30
  • What if one of the tokens is `rt4586`? Your solution will remove `rt458`, leaving the `6` behind (when in fact that token shouldn't be touched in the first place). –  Nov 11 '18 at 15:47
  • Oh yes, you're right. Updated my answer. Thank you for pointing it out. – Samir Selia Nov 12 '18 at 07:36