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?
-
Please include the DB schema and what you have attempted so farAndreas– Andreas2018-11-29 05:54:46 +00:00Commented Nov 29, 2018 at 5:54
-
SELECT trim(regexp_substr(PROP_VAL, '[^|]+', 1, level)) str FROM (SELECT '001|3880000005376|Personal ID| ||15-MAY-2006' PROP_VAL FROM DUAL) CONNECT BY instr(PROP_VAL, '|', 1, level - 1) > 0 i have to use a loop to replace the fourth value, can it be done by replace.kirthi kumar– kirthi kumar2018-11-29 05:57:03 +00:00Commented Nov 29, 2018 at 5:57
Add a comment
|
1 Answer
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.
3 Comments
kirthi kumar
thank for the quick response, what if i want to update the same table using the string.
Littlefoot
You're welcome. You'd just update it, e.g.
update test set col = regexp_replace(col, '[^|]+', 'NEW STRING', 1, 4);Littlefoot
No problem; I'm glad if it helped.