2

I have

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 and put it in a separate field.

My code above throws

"substring out of bounds"

error.

Help is greatly appreciated. Thank you.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
ysl
  • 51
  • 6
  • Can you show some actual data, looks like name-value-pairs? You might be able to switch to `nvp` like `Nvp(col2, 'ABCD','\ ~\ ', '#')` – dnoeth Jul 25 '17 at 17:50
  • fshjk#3~dhaj#NON-LOCAL~dhahdate#64030~kjqiwDATE2#64030`75:~kwokdNUM#000000010652~ncdj#158672694~pwkd#410~djakj#147~xkiww#322285781~lowqABCD#1025133646~jdoi#~pwdljae#25133648~pqeidjdt#04212016 – ysl Jul 25 '17 at 18:20

1 Answers1

1

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 ABCD in 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)
dnoeth
  • 59,503
  • 4
  • 39
  • 56