1

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?

ArtBajji
  • 949
  • 6
  • 14

1 Answers1

3

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 :
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • 1
    Assuming the key must be non-NULL but the payload (the value) may be NULL, it may be a bit safer to change the second `[^:]+` (but not the first one) into `[^:]*` - in both places that is used. Also, if eventually this solution is to be used with different inputs, `43` can be replaced with a bind variable. –  Jun 14 '19 at 16:57
  • @mathguy I agree. I just followed OP logic when choosing the quantifier. – Wiktor Stribiżew Jun 14 '19 at 16:58