How to check data in a column separated by a colon (:)?

I have an Oracle database column, say col1, that has the following values:

Col1 (A:B:C)

I now need to开发者_运维知识库 come along and add to this Col1, only if it doesn’t exist, additional values but unsure how to go about checking to see if Col1 already contains these values.

Scenario might be as follows:

1) Need to add B   => Outcome=> check Col1 – B exists, do not add.
2) Need to add A:C => Outcome=> check Col1 – A and C exists, do not add.
3) Need to add C:D => Outcome=> check Col1 – C exists but D doesn’t, do not add C but need to add D
4) Need to add G  => Outcome=> check Col1 – G doesn’t, need to add G

Using Oracle SQL or PL/SQL I am unsure how to go about processing the above to ensure whether items exist or don’t exist and whether to add or not to add to Col1

This might get you started:

UPDATE mytable
SET Col1 = Col1 || ':A'
WHERE INSTR(':' || Col1 || ':', ':A:') = 0;

To remove a value:

UPDATE mytable
SET Col1 = TRIM(':' FROM REPLACE(Col1, ':A:', ':'))
WHERE INSTR(':' || Col1 || ':', ':A:') > 0;