Oracle string replacement

oracle, plsql

Solution

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

Problem

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

Original source