How to get the last instance of a regular expression in MySQL / HeatWave?

Other Questions MySQL
1

I am using MySQL 8.0 with HeatWave and need to find the last occurrence of a pattern in my text. For instance, the pattern is like AuditKey: XXXXX-XXXX-XXXX-XXXX, and it can appear multiple times in a specific field.

SELECT field_name FROM my_table WHERE some_condition;
-- Result: text:1:status:ACTIVE:Example: AuditKey: ABCD-1234-5678-9XYZ|Data:1:Error: Issue: AuditKey: QRST-9876-5432-1ABC|

From the example given, my goal is to extract only the last AuditKey which in this case is QRST-9876-5432-1ABC. The AuditKey could be present 0, 1, or more times in the field.

In BigQuery, I could achieve this with:

ARRAY_REVERSE(REGEXP_EXTRACT_ALL(field_name, r'Audit[Key]*[=|: ]*([A-Za-z0-9]+-[A-Z0-9]+-[A-Z0-9]+-[A-Z0-9]+)'))[SAFE_OFFSET(0)]

However, MySQL does not have the REGEXP_EXTRACT_ALL function. I attempted to use REGEXP_SUBSTR(), but that only returns the first result. What is the proper way to retrieve the last occurrence of the AuditKey pattern in MySQL / HeatWave?

2

I’ve hit this same issue with MySQL pattern extraction. Since MySQL doesn’t have REGEXP_EXTRACT_ALL, you’ll need to get creative with string functions. Here’s what works for me:

SELECT REGEXP_SUBSTR(
    SUBSTRING(field_name, LOCATE('AuditKey:', field_name, LENGTH(field_name) - LENGTH(REPLACE(field_name, 'AuditKey:', '')) + 1 )),
    'AuditKey: ([A-Za-z0-9]+-[A-Z0-9]+-[A-Z0-9]+-[A-Z0-9]+)',
    1, 1, '', 1
) as last_audit_key
FROM my_table
WHERE some_condition;

It finds the last ‘AuditKey:’ position using LOCATE with an offset, then runs REGEXP_SUBSTR from there. The trick is using the length difference to grab the rightmost match instead of the first one.

Tested this on MySQL 8.0 - works fine whether you have zero, one, or multiple AuditKey entries. Performance isn’t bad for moderate datasets either.