pattern matching - REGEXP_SUBSTR - "substring out of bounds" error -

i have

select col1, ( regexp_substr ( col2, ' ( ?<=~ ) .*? ( ?=abcd ) ' )      || substring ( col2 position ( 'abcd' in col2 )      position ( '~' in substring ( col2 position ( 'abcd' in col2 ) ) ) -1 ) xyz)  db.table  col2 '%abcd%'; 

i have field values decribed in below pattern.

name1#value1 ~ name2#value2 ~ ......... ~ namex#valuex ~ ........... ~ namen#valuen

there no limit number of name&value sections. 1 such name have 'abcd' pattern. want extract section of name , value contains 'abcd' pattern , put in separate field.

my code above throws

"substring out of bounds"

error.

help appreciated. thank you.

as you're looking pattern , exact name can't use nvp, there's no need mixing regexp_sub , substring.

this regex (~|^)([^~]?abcd.?#.*?)(~|$) finds 1st ~name#value~ pattern contains abcd in it's name:

trim(both '~' regexp_substr(col2, '(~|^)([^~]*?abcd.*?)(~|$)',1,1,'i')) 

the 'i' indicates case insensitive search.

if release supports (undocumented) regexp_substr_gpl there's no need trim because supports returning specific capturing group:

regexp_substr_gpl( col2, '(~|^)([^~]*?abcd.*?#.*?)(~|$)',1,1,'i',2)