CODEWITHSUNDEEP
oracle-databaseregexp-replace
Bolimera Hannah
Bolimera Hannah

Reputation: 195

oracle regexp_replace delete last occurrence of special character

I have a pl sql string as follows : 

String := 'ctx_ddl.add_stopword(''"SHARK_IDX19_SPL"'',''can''); 

       create index "SCOTT"."SHARK_IDX2"
       on "SCOTT"."SHARK2"
       ("DOC")
          indextype is ctxsys.context
          parameters(''
            datastore       "SHARK_IDX2_DST"
           filter          "SHARK_IDX2_FIL"
           section group   "SHARK_IDX2_SGP"
           lexer           "SHARK_IDX2_LEX"
           wordlist        "SHARK_IDX2_WDL"
           stoplist        "SHARK_IDX2_SPL"
           storage         "SHARK_IDX2_STO"
           sync (every "SYSDATE+(1/1)" memory 67108864)
           '')
           /
       ';

I have to get search the final occurrence of '/' and add ';' to it. Also I need to escape the quotes preset in parameters ('') to have extra quotes. I need output like 

String := 'ctx_ddl.add_stopword(''"SHARK_IDX19_SPL"'',''can''); 

       create index "SCOTT"."SHARK_IDX2"
       on "SCOTT"."SHARK2"
       ("DOC")
          indextype is ctxsys.context
          parameters(''''
            datastore       "SHARK_IDX2_DST"
           filter          "SHARK_IDX2_FIL"
           section group   "SHARK_IDX2_SGP"
           lexer           "SHARK_IDX2_LEX"
           wordlist        "SHARK_IDX2_WDL"
           stoplist        "SHARK_IDX2_SPL"
           storage         "SHARK_IDX2_STO"
           sync (every "SYSDATE+(1/1)" memory 67108864)
           '''')
           /;
       ';

Any help.

Upvotes: 0

Views: 1170

Answers (1)

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

There's an age-old saying: "Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems"

Unless you are confronted by a problem that truly requires regular expressions, I'd recommend working with basic string manipulation functions.

Semi-colon: Use INSTR to find last occurence of '/', call this P1.
Result = Substr from position 1 through P1||';'||substr from P1+1 through to end-of-string

Parameters substitution: Use INSTR to find where parameter list starts (i.e. find "parameters(" in your string) and ends (presumably the last closing parenthesis ")" in your string). Call these P2 and P3.

Result = substr from 1 through P2 || REPLACE(substr from P2+1 through P3-1,'''','''''''') || substr from P3 to end-of-string

Upvotes: 1

Related Questions