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.

Answer 1

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 |