How to use Oracle 10g REGEXP_SUBSTR to extract a string using a string delimiter

Question

I have many strings that I need to tokenise - an example is shown below.

NAME:ALAN GREYINTACC:999ACCOUNT:12345678SORT:654321REF:ABC 7654321

I can use a combination of INSTR and SUBSTR to do this, but I was hoping that there might be an easier way to perform this using REGEXP_SUBSTR.

I've looked at anchors, but could only find references to start string ^ and end string $.

Is there such a thing as an 'in string' anchor? e.g. If I wanted the account number from this string, I want everything after ACCOUNT: and before SORT:

Any replies appreciated,

Many Thanks

Sabuj Hassan · Accepted Answer · 2014-03-26 12:31:35Z

2

Use REGEXP_REPLACE instead for this purpose. This can help you to capture groups(like \1) from the matching and use it in replacement. See below example:

SELECT
  REGEXP_REPLACE('NAME:ALAN GREYINTACC:999ACCOUNT:12345678SORT:654321REF:ABC 7654321',
                  '.*ACCOUNT:(.+?)SORT.*',
                  '\1') "SOME_VALUE"
FROM DUAL;

answered Mar 26, 2014 at 12:31

Sabuj Hassan

39.7k14 gold badges83 silver badges88 bronze badges

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

1 Comment

Drumbeg Over a year ago

I'd just worked this out by trying to solve the question. REGEXP_SUBSTR does not allow for capturing of matches, so REGEXP_REPLACE is the way to go. +1.

Robin · Accepted Answer · 2014-03-26 12:27:41Z

0

There's no 'in string' anchor per say: there are patterns that your regex can match. Even 'anchor' are really matches for the end and beginning of the line.

This regex, for example, will match ACCOUNT: followed by anything, followed by SORT: and store this 'anything' in the first capturing group (indicated in the regex with the parenthesis)

ACCOUNT:(.*?)SORT:

. is any character (except newline)
* is a quantifier, means 0 or more times
? makes the quantifier lazy (will stop at the shortest match, useful if your data is ACCOUNT:123SORT:456SORT:789 and you only want to select 123, not 123SORT:456)

edited Mar 26, 2014 at 12:27

answered Mar 26, 2014 at 12:07

Robin

9,7063 gold badges38 silver badges45 bronze badges

4 Comments

user3463817 Over a year ago

Thanks Robin. Unfortunately, due to the state of the data I'm being supplied with, there can be pretty much anything between ACCOUNT: and SORT: so I can't just look for numbers.

Robin Over a year ago

@user3463817: I see. I updated my answer accordingly, but I'm not familiar with oracle's regex and I don't know how to display the captured value. Good luck

Drumbeg Over a year ago

Having played around with REGEXP_SUBSTR, I couldn't figure a way of actually using the capture. As Sabuj suggests, REGEXP_REPLACE is required for this.

user3463817 Over a year ago

Thank you all very much for answering this question. Much appreciated! REGEXP_REPLACE it is!!!!

Collectives™ on Stack Overflow

How to use Oracle 10g REGEXP_SUBSTR to extract a string using a string delimiter

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related