CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
Prateek Gautam
Prateek Gautam

Reputation: 344

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?

Upvotes: 1

Views: 370

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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;

Upvotes: 2

Related Questions