A History Log KEYS column stores a composite string like `COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^`, and each named value's length varies record to record (some are 1 digit, some are 2), so a fixed-position SUBSTRING approach no longer reliably extracts them — how is each named value extracted regardless of its length?

Q: A History Log KEYS column stores a composite string like `COMPANY=01^LUMP_SUM_TRANS_ID=2^MIXED_PAYMENT_ID=4637^MIXED_PAYMENT_LEDGER_TRANS_ID=25^`, and each named value's length varies record to record (some are 1 digit, some are 2), so a fixed-position SUBSTRING approach no longer reliably extracts them — how is each named value extracted regardless of its length?

Use REGEXP_SUBSTR, targeting the specific key name followed by = and capturing everything up to (but not including) the next ^ delimiter: REGEXP_SUBSTR(keys_string, 'KEY_NAME=([^^]+)', 1, 1, NULL, 1), substituting KEY_NAME for whichever specific key you need (e.g. COMPANY, LUMP_SUM_TRANS_ID, MIXED_PAYMENT_ID, MIXED_PAYMENT_LEDGER_TRANS_ID). This reliably extracts each named value regardless of how many digits it happens to have, unlike a fixed-position substring approach that assumes every value is the same length.

Answer

Use REGEXP_SUBSTR, targeting the specific key name followed by = and capturing everything up to (but not including) the next ^ delimiter: REGEXP_SUBSTR(keys_string, 'KEY_NAME=([^^]+)', 1, 1, NULL, 1), substituting KEY_NAME for whichever specific key you need (e.g. COMPANY, LUMP_SUM_TRANS_ID, MIXED_PAYMENT_ID, MIXED_PAYMENT_LEDGER_TRANS_ID). This reliably extracts each named value regardless of how many digits it happens to have, unlike a fixed-position substring approach that assumes every value is the same length.

← Browse Security and access questions