CODEWITHSUNDEEP
oracle-databaseplsql
AJM
AJM

Reputation: 32490

Oracle string replacement

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

Upvotes: 3

Views: 13143

Answers (3)

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

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

Upvotes: 9

andri
andri

Reputation: 11292

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.)

Upvotes: 4

Vincent Malgrat
Vincent Malgrat

Reputation: 67792

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

Upvotes: 2

Related Questions