Regarding Oracle Regexp_Replace

Question

I've a string like "Vinoth^Vinoth Karthick Vinoth^Vinoth^Vinoth" delimited by "^". I would like to replace only Vinoth by XXX.

I/P String : Vinoth^Vinoth Karthick Vinoth^Vinoth^Vinoth
Expected output : XXX^Vinoth Karthick Vinoth^XXX^XXX

Please suggest how to do this using Regexp_replace or any other function in ORACLE SQL Statement.

you should be able to replace the string including the delimiter with a new string also including the delimiter with REPLACE. you may also feel compelled to use the REGEX replace option if your pattern is more complex. — Randy
– Randy, Commented Apr 26, 2018 at 12:55
Which one do you want to changed ^Vinoth or Vinothas pattern string? Since, i think Expected output should be XXX^Vinoth Karthick XXX^Vinoth^Vinoth or VinothXXX Karthick VinothXXXXXX ... — Barbaros Özhan
– Barbaros Özhan, Commented Apr 26, 2018 at 13:32
I/P String : Vinoth^Vinoth Karthick Vinoth^Vinoth^Vinoth Expected output : XXX^Vinoth Karthick Vinoth^XXX^XXX — Vinoth Karthick
– Vinoth Karthick, Commented Apr 26, 2018 at 13:35

MT0 · Accepted Answer · 2018-04-26 13:20:30Z

Double up the delimiter ^ characters and wrap the string in delimiter ^ characters so that each element has its own distinct leading and trailing delimiter then you can just replace ^Vinoth^ with ^XXX^ and reverse the doubling of the delimiters and trim the leading and trailing delimiters:

SQL Fiddle

Oracle 11g R2 Schema Setup:

SELECT 1 FROM DUAL;

Query 1:

SELECT TRIM(
         '^' FROM
         REPLACE(
           REPLACE(
             '^' ||
             REPLACE(
               'Vinoth^Vinoth Karthick Vinoth^Vinoth^Vinoth',
               '^',
               '^^'
             )
             || '^',
             '^Vinoth^',
             '^XXX^'
           ),
           '^^',
           '^'
         )
       ) AS replaced
FROM   DUAL

Results:

|                           REPLACED |
|------------------------------------|
| XXX^Vinoth Karthick Vinoth^XXX^XXX |

Littlefoot · Accepted Answer · 2018-04-26 18:01:13Z

0

Yet another option:

SQL> with test (col) as
  2    (select 'Vinoth^Vinoth Karthick Vinoth^Vinoth^Vinoth' from dual),
  3  inter as
  4    (select regexp_substr(replace(col, '^', ':'), '[^:]+', 1, level) col,
  5            level lvl
  6     from test
  7     connect by level <= regexp_count(col, '\^') + 1
  8    )
  9  select listagg(regexp_replace(col, '^Vinoth$', 'XXX'), '^')
 10    within group (order by lvl) result
 11  from inter;

RESULT
-----------------------------------------------------------------------------

XXX^Vinoth Karthick Vinoth^XXX^XXX

SQL>

answered Apr 26, 2018 at 18:01

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

3 Comments

MT0 Over a year ago

This fails on Vinoth^Vinoth Karthick :Vinoth^Vinoth^Vinoth

Littlefoot Over a year ago

And probably on quite a few other examples, @MT0, but that's not what the OP said to be the input.

MT0 Over a year ago

OP wanted to replace an entire element of a ^ delimited string - you are changing the delimiter (when you do not need to) and it is now treating it as a delimited list where the delimiter is either ^ or :, which is incorrect. Change the regular expression to search for non-caret characters and you won't have the problem but just dismissing the issue out of hand as it does not match the narrow example the OP gave rather than the intent of the question is disingenuous and does not help future users with a similar problem.

Collectives™ on Stack Overflow

Regarding Oracle Regexp_Replace

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related