I have a multi-valued column , MISC_CONTENT with the following string in the column:
amount = 7995 ;channel = SXXXN21 ;group_header = NPS099 ;currency = EUR
How can retrieve the value NPS099 by lookup with group_header?
Asked
Active
Viewed 78 times
0
user2102665
- 429
- 2
- 11
- 26
-
use the SQL LIKE operator – jdigital Nov 04 '19 at 03:35
-
LIKE doesn't work in multi-valued, and the length value/sequence of group_header is random – user2102665 Nov 04 '19 at 03:39
-
1You said _How can retrieve the value NPS099 by lookup with group_header?_ but it sounds like you're asking how to retrieve the value associated with `group_header`. if so, the regexp_substr answer below from @TimBiegeleisen looks like a reasonable approach – jdigital Nov 04 '19 at 03:53
1 Answers
0
We can try using REGEXP_SUBSTR:
WITH yourTable AS (
SELECT 'amount = 7995 ;channel = SXXXN21 ;group_header = NPS099 ;currency = EUR' AS col FROM dual
)
SELECT
REGEXP_SUBSTR(col, '(^|\s|;)group_header = (.*?)\s*(;|$)', 1,1,NULL,2)
FROM yourTable;
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
Thanks, it works. Is there any restriction on the substring from the syntax? some of the values are truncated in my result – user2102665 Nov 04 '19 at 05:02
-
I can only answer based on data which I can actually. My answer works for what you showed us. If you have an edge case in mind, then please reveal it. – Tim Biegeleisen Nov 04 '19 at 05:04
-
it truncated when hit the whitespace, for, example, ABCD EFGH become ABCD – user2102665 Nov 04 '19 at 05:08