Extracting string between two characters in sql oracle database-CodePudding

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.

CodePudding user response：

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.