How to extract a figure from a long line of text with different kinds of characters using REGEXREPLACE in ACL (Highbond)

Question

I have strings in this format in a column called "Charge_Details" with minor differences: SBLC FEE <> 1 - 360 day*!<> 400,000.00 USD<> 08/02/18 - 17/12/18 312 <>9

I'm trying to extract the amount before USD using REGEX functions. The application I'm using (ACL from Highbond) only has REGEXFIND and REGEXREPLACE with these syntaxes: REGEXFIND(string, pattern) and REGEXREPLACE(string, pattern, new_string) functions. You can ignore the ACL scripting parts of this request. I just need help with the REGEX part.

It uses the same REGEX rules as with any other application.

I have tried removing the "<<>>" and then trying to return only the amount to no avail.

I first created another column (CATEGORY) replacing all the "<>" with "++" and "||" and excluding "!" EXCLUDE( ALL(REGEXREPLACE(ALL(REGEXREPLACE(charge_details,"<<(\w\w\w\w)>>\s\d{1,2}/\d\d/\d\d", "++")), "<<(\w\w\w\w)>>", "||")), "!") IF FIND("!", CHARGE_DETAILS)

The result: SBLC FEE|| 1 - 360 day|| 400,000.00 USD++ - 17/12/18 312 ||9

When I try to split the new string column using the "||" as a delimiter, it only returns "SBLC FEE" in the first segment and blanks in any other segment.

I then tried to extract the amount from the CATEGORY column I created using: REGEXREPLACE(CATEGORY, "(\d{0,3})(,\d{3})*(.\d\d)(\s\w\w\w)|)","$1$2$3$4")

It just returns everything in the CATEGORY column instead of the four groups.

Thanks.

Answer 1

This example is in Oracle, but should help you with the regex. The WITH clause just sets up test data. The regex says, match zero or more characters followed by a space followed by one or more digits which are followed by zero or more characters (non-greedily) until followed by the literal 'spaceUSD' then anything else. Note the parens around the 'digits which are followed by zero or more characters' part. That signifies a "remembered" group. Replace the entire string with the remembered group #1.

with tbl(str) as (
  select 'SBLC FEE <> 1 - 360 day*!<> 400,000.00 USD<> 08/02/18 - 17/12/18 312 <>9'
   from dual
 )
 select regexp_replace(str, '.* (\d+.*?) USD.*', '\1') as USD
 from tbl;


USD       
----------
400,000.00
1 row selected.