Oracle string replacement

Question

I have a column in my oracle database which, for my reasons, do not match my structure, contain a CSV string, for example.

Item a, element b, element c, element d

I want to run the UPDATE statement to get rid of the c element. Thus ending

Element a, element b, element d

How can i achieve this

Answer 1

You can use the Oracle REPLACE function :

UPDATE table
SET col = replace(col, 'item c', '')

You just need to be careful to treat it as part of the CSV, for example by removing the next comma. This could mean replacing “item c,” first, and then replacing “item c” to capture both cases.

---

: , , , . CSV - , Vincent ,

Answer 2

Oracle ( , Oracle 10), REGEXP_REPLACE:

UPDATE table SET column = REGEXP_REPLACE(column,'[^\,]+,','',1,3)

(, , , CSV .)

Answer 3

you can use a combination of INSTR and SUBSTR to remove the third field:

SQL> WITH a AS (SELECT 'Item a,Item b,Item c,Item d' col FROM dual)
  2   SELECT substr(col, 1, instr(col, ',', 1, 2))
  3          || substr(col, instr(col, ',', 1, 3) + 1) sub
  4     FROM a;

SUB
--------------------
Item a,Item b,Item d