Sql to remove a substring within a string (oracle 11g)

Question

I have a sample string below.

"MHV9DRDUY7 Confirmed.on 31/8/18 at 10:18 AMKsh9,500.00 received from 25470000000 JAMES BOND.New Account balance is Ksh12,050,100"

I would like to remove the substring "New Account balance is Ksh12,050,100" from the main string using SQL.

Please note that the amount in the substring (Ksh12,050,100) is not fixed, hence I am having a challenge using REGEXP_REPLACE.

Answer 1

Assuming that the substring to remove always is in the final part of the string, and that the substring 'New Account balance is Ksh' at most occurs once in the string, you don't need regular expressions.

You can find the position where the string to remove starts (by INSTR) and then trim the string up to that position (SUBSTR):

select substr(yourString, 1, instr(yourString, 'New Account balance is Ksh') -1)
from ...