Oracle Query on Regexp_substr

Question

I'm trying to use Regexp_substr to extract the unit no from the Property Field. Below is the query.

regexp_substr(PROPERTY,'(#)[^\S]+ ')

While some of the results are correct, but others came back like below:

#05-08 DOVER PARKVIEW

May I know what do I need to do to omit all the alphabets or wordings? Thank you.

So what is the input string and what is the result you need? BTW, is it Oracle 11g/12g or older? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 23, 2017 at 7:07
I'm trying to match the unit number only, which is #05-08. But this is just one format, there are other unit numbers which looks like #19-1215 or #06-143 — Adrian Tan
– Adrian Tan, Commented May 23, 2017 at 7:46

Wiktor Stribiżew · Accepted Answer · 2017-05-23 07:52:39Z

1

The [^\S] pattern matches any char but a \ and a capital letter S.

You need to capture 1+ digits followed with - and again 1+ digits after a # and extract that part of the match only with

regexp_substr(PROPERTY, '#(\d+-\d+)', 1, 1, NULL, 1)

The last 1 argument tells the regex engine to extract the contents of the capturing group with ID 1 (it is the only capturing group in the pattern anyway).

# - a literal #
(\d+-\d+) - Capturing group 1 matching:
- \d+ - 1 or more digits
- - - a hyphen
- \d+ - 1 or more digits.

See the online demo:

select regexp_substr('#05-08 DOVER PARKVIEW', '#(\d+-\d+)', 1, 1, NULL, 1) as RESULT from dual

answered May 23, 2017 at 7:52

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Oracle Query on Regexp_substr

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related