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, May 23 '17 at 07: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, May 23 '17 at 07:46

score 1 · Accepted Answer · answered May 23 '17 at 07:52

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

Oracle Query on Regexp_substr

1 Answers1