Search code examples
regexoracle-databaseoracle12cregexp-substr

Validating subexpression in Oracle REGEXP_SUBSTR

Oracle 12cR1

I have a column with lengthy strings like below

:Key1:Value1:Key2:Value2:Key3:Value3:Key4...…..

The keys are unique numbers. The values are any string value which can even be a number same as any other key. If I give a key i must get its corresponding value. For example,

lengthy_str_col := ':101:abc:12:43:43:101.4:4:bus'

For getting the value for the key, 43, I tried the following.

SELECT REGEXP_SUBSTR(lengthy_str_col,'(:([^:])+)(:[^:]+)') FROM DUAL;

This gives me the first key-value pair ':1:abc'. Now as I know that 43 is the third key, I used

SELECT REGEXP_SUBSTR(lengthy_str_col,'(:([^:])+)(:[^:]+)', 1, 3, 'i', 4) FROM DUAL;

to get the value 101.4 for the key 43.

But a required key can be at any random position. Is there a way to get the value for any given key?

Solution

  • You may use

    select REGEXP_SUBSTR(':101:abc:12:43:43:101.4:4:bus', '^(:[^:]+:[^:]+)*?:43:([^:]+)', 1, 1, NULL, 2) as Result from dual

    See this regex demo and this regex graph:

    enter image description here

    Explanation

    • ^ - start of a string
    • (:[^:]+:[^:]+)*? - zero or more, but as few as possible, occurrences of two repetitions of : and 1+ chars other than :
    • :43: - a key in between colons
    • ([^:]+) - Group 2 (the result): 1 or more chars other than :