How to replace word-CodePudding

I am trying to replace the word in the string. I have an example string. EX.

DMG RENT FTR * to BLANK
DMG RENT FTR = 1; to BLANK
DMG RENT FTR = 1.45; to blank

Before conversion:

ADJ BASE RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR * DMG RENT FTR *  MED EXP ILF * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
BASE RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44; DMG RENT FTR = 1; MED EXP ILF = 1; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1
CUSTOME  RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR * DMG RENT FTR *  MED EXP ILF * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
CUSTOME RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44; DMG RENT FTR = 1.45; MED EXP ILF = 1; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1

when i try to replace using regular_replace and replace function that through replaces only "DMG RENT FTR" this word.

I would like to get some thing like this.

After conversion:

ADJ BASE RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR *   MED EXP ILF * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
BASE RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44;  MED EXP ILF = 1; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1
CUSTOME  RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR *   MED EXP ILF * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
CUSTOME RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44;  MED EXP ILF = 1; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1

CodePudding user response：

Nest REPLACEs is one - simple - option:

Sample data:

SQL> WITH
  2     test (col)
  3     AS
  4        (SELECT 'ADJ BASE RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR * DMG RENT FTR *  MED EXP ILF * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
  5  BASE RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44; DMG RENT FTR = 1; MED EXP ILF = 1; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1
  6  CUSTOME  RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR * DMG RENT FTR *  MED EXP ILF * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
  7  CUSTOME RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44; DMG RENT FTR = 1.45; MED EXP ILF = 1; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1'
  8           FROM DUAL)

Query:

  9  SELECT REPLACE ( REPLACE (REPLACE (col, 'DMG RENT FTR *'      , ''),
 10                                          'DMG RENT FTR = 1;'   , ''),
 11                                          'DMG RENT FTR = 1.45;', '') result
 12    FROM test;

RESULT
--------------------------------------------------------------------------------
ADJ BASE RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR *   MED EXP IL
F * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
BASE RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44;  MED EXP ILF = 1; M
ISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1
CUSTOME  RATE = ROUND(( BASE RATE * COV CHG FTR * IL LESS DED FTR *   MED EXP IL
F * MISC CRDT FTR * AMEND LL EXCL FCTR),3)
CUSTOME RATE = 43.487; COV CHG FTR = 1; IL LESS DED FTR = 2.44;  MED EXP ILF = 1
; MISC CRDT FTR = 1; AMEND LL EXCL FCTR = 1


SQL>

CodePudding user response：

You want to replace 'DMG RENT FTR' plus all following characters until you meet a letter (M of MED in your examples). In other words: remove 'DMG RENT FTR' plus all following non-letters. That is:

REGEXP_REPLACE(str, 'DMG RENT FTR[^A-Z]*')