I have a partial resolution, which I will share with you all,
because that's just the sort of big-hearted guy I am.
The solution will work with the original example, but in this post my
example changes to be more compact, and to search for several different
ranges at once (which is the actual requirement I have):
values regexp_substr('312345 41234 1123456',
'(?<!\d)(1\d{6}|[2-3]\d{5}|4\d{4})(?=\D|$)',1,3)
The relevant part is the change from a negative look-ahead for a digit:
(?!\d) in my original example, to a positive look-ahead for either a
*non*-digit
or end of line: (?=\D|$)
So, the above statement means: Substring out:
- A seven-digit number starting with 1
- OR a six-digit number starting with 2 or 3
- OR a five-digit number starting with 4.
- And only if it's not preceded by a number or followed by a number.
- And don't return the preceding/following character being checked for (the
motivation for look-arounds).
In this particular example I'm searching for the third instance and I've
deliberately put the three numbers out of order in the string to make a
better test. So far it's handled anything I've thrown at it.
I think I just have to accept that the rules are different for REGEXP_INSTR
than they are for REGEXP_SUBSTR.
--
Arnie
midrange.com
