Search code examples
sqloracleoracle11goracle-sqldeveloper

How to extract a substring from column in oracle?

I've a column in oracle that stores values in keys. Just for example-

Column_name
((key1="value1" AND key2='value1') OR (key1="value1" AND key2='value2'))
((key1="null" AND key2='value3') OR (key1="value1" AND key2='value4'))

I want to only extract the value of key2 before OR clause (as there are 2 key2 in every row of this column)

Expected result:

Column_name Value
((key1="value1" AND key2='value1') OR (key1="value1" AND key2='value2')) value1
((key1="null" AND key2='value3') OR (key1="value1" AND key2='value4')) value3

Can somebody give me roughly an idea how to do this?

Solution

  • Assuming we can describe your logic as extracting the first key2 value, we can try using REGEXP_SUBSTR with a capture group:

    SELECT col, REGEXP_SUBSTR(col, 'key2=''(.*?)''', 1, 1, NULL, 1) AS key
FROM yourTable;