Oracle string replacement

Question

I have a column in my oracle database which due reasons beyond my control contains a CSV string e.g.

Item a,Item b,Item c,Item d

I want to run an UPDATE statement to get rid of item c. Thus ending up with

Item a,Item b,Item d

How can I achieve this

Answer 1

You could use the Oracle REPLACE function:

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

You just need to be careful handling it as part of a CSV, e.g stripping a following comma. This could mean replacing 'item c,' first and then replacing 'item c' to capture both cases.

---

EDIT: ah, I might have misunderstood. My solution is based on removing a particular string from your CSV - if you are looking to always replace the 3rd item then Vincent's answer is the one you'll need

Answer 2

Hi AJM,

you could 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

Answer 3

If you have a fairly recent version of Oracle (I believe regular expressions were introduced in Oracle 10), you can use REGEXP_REPLACE:

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

(Also, please do violent things to the genius who stored CSV this way in a relational database.)