sqlregexsubstringregexp-substr

Extracting string between two characters in sql oracle database


I need to extract a string that will located between two characters, with always the same pattern

sample string:

A CRN_MOB_H_001 a--> <AVLB>

What is in bold AVLB is what I want to extract, the whole string will always have the same pattern, and everything that is before the < is irrelevant to me. The string will always have the same pattern:

Some string with possible special characters such as <>, although very unlikely so, it can be ignored if too complicated

a space

then -->

a space

and then the part that is interesting <XXXXXXX>

The XXXXXXX representing the part I want to extract

thank you for your time.

I have tried several things, could not get anywhere I wanted.


Solution

  • Please try this REGEXP_SUBSTR(), which selects what is in the angled brackets when they occur at the end of the string.

    Note the WITH clause just sets up test data and is a good way to supply data for people to help you here.

    WITH tbl(str) AS (
      SELECT 'A CRN_MOB_H_001 a--> <AVLB>' FROM dual
    )
    SELECT REGEXP_SUBSTR(str, '.*<(.*)>$', 1, 1, NULL, 1) DATA
    FROM tbl;
    
    
    DATA
    ----
    AVLB
    1 row selected.