0
I have one column that contains the value in the key-value pair. But I need to extract only value from that column.
Input Table:
+------+---------------------------------+
| Col1 | Col2 |
+------+---------------------------------+
| 1 | key1:Val1;Key2:Val2;;;Key5:Val5 |
+------+---------------------------------+
Expected Output:
+------+--------------------------------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |
+------+--------------------------------+------+------+------+------+
| 1 | key1:Val1;Key2:Val2;;Key4:Val4 | Val1 | Val2 | | Val4 |
+------+--------------------------------+------+------+------+------+
Explanation:
As Col2 contains the value in key-value pair out of which I need to extract only value and store it in a different column and wherever there is no value we need to put it as blank. So basically, need to identify ; and :.
My attempt:
SELECT
Col1,
SUBSTR(REGEXP_SUBSTR(Col1, 'Key1:(.*?)(;|$)', 1, 1, NULL, 1), 5) AS Col2
From table_name;
DB - Oracle Sql Developer tool
Version - 19c
I am not sure whether this is the optimized way to handle this requirement and hence seeking help for the same.