SyntaxEditor Code Snippet
select col1, ( REGEXP_SUBSTR ( col2, ' ( ?<=~ ) .*? ( ?=ABCD ) ' ) || SUBSTRING ( col2 FROM POSITION ( 'ABCD' IN col2 ) FOR POSITION ( '~' IN SUBSTRING ( col2 FROM POSITION ( 'ABCD' IN col2 ) ) ) -1 ) as xyz) from db.table where col2 like '%ABCD%';
I have a field with values as decribed in below pattern.
Name1#Value1 ~ Name2#Value2 ~ ......... ~ NameX#ValueX ~ ........... ~ NameN#ValueN
There is no limit for number of name&value sections. One such Name will have 'ABCD' pattern.
I want to extract that section of name and value which contains the 'ABCD' pattern.
My code above throws "substring out of bounds" error.
Help is greatly appreciated. Thank you.
Answered on StackOverflow:
As you're looking for a pattern and an exact name you can't use NVP, but there's no need for mixing REGEXP_SUB and SUBSTRING.
This regex (~|^)([^~]?ABCD.?#.*?)(~|$) finds the 1st ~name#value~ pattern which contains ABCDin it's name:
Trim(Both '~' FROM RegExp_Substr(col2, '(~|^)([^~]*?ABCD.*?)(~|$)',1,1,'i'))
The 'i' indicates a case insensitive search.
If your release supports the (undocumented) RegExp_Substr_gpl there's no need to trim because it supports returning a specific capturing group:
RegExp_Substr_gpl( col2, '(~|^)([^~]*?ABCD.*?#.*?)(~|$)',1,1,'i',2)