CODEWITHSUNDEEP
sqloracle-database
kirthi kumar
kirthi kumar

Reputation: 9

Replace a column value in a string delimiter using Oracle SQL

I have a string value in a column in a table like 

001|3880000005376|Personal ID| ||15-MAY-2006

and I want to replace the fourth value by another string value 'ABCDEF' , can it be possible by a single update or by PL/SQL program?

Upvotes: 0

Views: 446

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Here's one option:

SQL> with test (id, col) as
  2  (select 1, '001|3880000005376|Personal ID| ||15-MAY-2006'   from dual union all
  3   select 2, '002|3880000005376|Personal ID|XXX||15-MAY-2007' from dual
  4  )
  5  select
  6    id,
  7    regexp_replace(col, '[^|]+', 'NEW STRING', 1, 4) result
  8  from test;

        ID RESULT
---------- ------------------------------------------------------------
         1 001|3880000005376|Personal ID|NEW STRING||15-MAY-2006
         2 002|3880000005376|Personal ID|NEW STRING||15-MAY-2007

SQL>

It replaces 4th occurrence of the '[^|]+' pattern with a NEW STRING value.

Upvotes: 1

Related Questions