How to replace string using Regexp_Replace in oracle

Question

I want to replace this:

"STORES/KOL#10/8/36#1718.00#4165570.00#119539388#PT3624496#9902001#04266#6721#PT3624496-11608091-1-55-STORES/KOL"

with this:

"STORES/KOL#10#8#36#1718.00#4165570.00#119539388#PT3624496#9902001#04266#6721#PT3624496-11608091-1-55-STORES/KOL"

basically this is conditional based replace I want to replace / with # like STORES/KOL string should be STORES/KOL but 10/8/36 string should be 10#8#36

Answer 1

This will replace the 2nd and 3rd / character with a #:

Oracle Setup:

CREATE TABLE test_data ( value ) AS
SELECT '"STORES/KOL#10/8/36#1718.00#4165570.00#119539388#PT3624496#9902001#04266#6721#PT3624496-11608091-1-55-STORES/KOL"'
FROM   DUAL;

Query:

SELECT REGEXP_REPLACE(
         value,
         '^(.*?/.*?)/(.*?)/(.*)$',
         '\1#\2#\3'
       ) AS replacement
FROM   test_data

Output:

| REPLACEMENT                                                                                                       |
| :---------------------------------------------------------------------------------------------------------------- |
| "STORES/KOL#10#8#36#1718.00#4165570.00#119539388#PT3624496#9902001#04266#6721#PT3624496-11608091-1-55-STORES/KOL" |

db<>fiddle here