CODEWITHSUNDEEP
sqldatabaseoracle-database
Kritika Gupta
Kritika Gupta

Reputation: 23

Replace character that occurs right after nth occurrence of character in oracle sql

For Eg : DS/Home/INT01/int_4/123045_PDF_test 12/06/2022 checklist

Here i want to replace / into '_' after 4th occurrence of /

Expected output: DS/Home/INT01/int_4/123045_PDF_TEST 12_06_2022 checklist

Upvotes: 1

Views: 194

Answers (1)

user5683823
user5683823

Reputation:

Something like this (using only standard string functions):

with
  test_strings (str) as (
    select 'DS/Home/INT01/int_4/123045_PDF_test 12/06/2022 checklist'
                                     from dual union all
    select null                      from dual union all
    select 'text/with/four/slashes/' from dual union all
    select 'only/two/slashes'        from dual union all
    select 'no slashes'              from dual union all
    select '////////'                from dual
  )
select case instr(str, '/', 1, 4) when 0 then str
         else substr(str, 1, instr(str, '/', 1, 4)) ||
              translate(substr(str, instr(str, '/', 1, 4) + 1), '/', '_')
       end as new_str
from   test_strings
;

NEW_STR                                                     
------------------------------------------------------------
DS/Home/INT01/int_4/123045_PDF_test 12_06_2022 checklist

text/with/four/slashes/
only/two/slashes
no slashes
////____

The same can easily be made into a user-defined function if you need the occurrence specifier (4 in your example), the "from-character" and the "to-character" to be generic.

Upvotes: 4

Related Questions