I have a table with a column having multi line value and each line is kind of key value pair (separated by = sign and probably ending with new line breaks)
Example value in one cell in the column:
| List of key value pair |
|---|
| key00=value00 <\n> key01=value01 <\n> key02=value02 |
I am looking for a SQL (Oracle) query to find the specific key (say Key01) and display it in following format
| KEY | VALUE |
|---|---|
| Key01 | Value01 |
Please help.
Here's one option
Sample data:
SQL> select * from test;
ID COL
---------- --------------------------------------------------
1 key00=value00
key01=value01
key02=value02
Subquery returns substring that begins with the "key" value (passed as a parameter), while outer query splits that substring into key and value themselves:
SQL> select regexp_substr(str, '^\w+') key,
2 regexp_substr(str, '\w+$') value
3 from (select regexp_substr(col, '&par_key=\w+') str
4 from test
5 );
Enter value for par_key: key01
KEY VALUE
--------------- ---------------
key01 value01
SQL>