REGEXP_SUBSTR - "substring out of bounds" error

Database
ysl
New Member

REGEXP_SUBSTR - "substring out of bounds" error

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%';

Hi,

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.

1 REPLY
Junior Contributor

Re: REGEXP_SUBSTR - "substring out of bounds" error

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)