Extracting string between two characters in sql oracle database

Question

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.

so what have you tried? Please post a minimal reproducible example — OldProgrammer
– OldProgrammer, Commented Dec 15, 2022 at 14:21

Gary_W · Accepted Answer · 2022-12-15 14:21:48Z

0

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.

answered Dec 15, 2022 at 14:21

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lass Over a year ago

this works great thank you very much, it even works if the beginning of my string contains some unwanted < or >, thanks again!

Collectives™ on Stack Overflow

Extracting string between two characters in sql oracle database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related