4

I'm writing a PL/SQL Oracle procedure that looks for possible customer numbers in a column. Customer numbers are 7 digits long, and could be prefixed or suffixed with any number of characters. However some of the values contain > 7 digit numbers, and in these cases I want to ignore them. So "A/C 1234567" and "Cust1234567B" should return a match for customer number 1234567, but "01234567" and "123456789" should not.

I am using \d{7} but this is returning a match on all the examples, so am looking for something similar to (?<!\d)\d{7}(?!\d) - but negative lookahead and lookbehind aren't supported. Any suggestions?

Þaw
  • 2,047
  • 4
  • 22
  • 39
Laurence
  • 55
  • 1
  • 4

2 Answers2

6

Without lookahed and lookbehind assertions available you could try

(^|\D)\d{7}(\D|$)

http://sqlfiddle.com/#!4/d41d8/12114/0

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • Try the following: SELECT 'Matched' AS RESULT FROM DUAL WHERE REGEXP_LIKE(' 1234567 ', '(?:^|\D)\d{7}(?:\D|$)'); ...does not return Matched – Laurence Jun 10 '13 at 05:08
  • In my testing I have found it's not returning exactly what I need, e.g. "Cust1234567B" returns "t1234567B". So I am surrounding it with another REGEXP_SUBSTR such as REGEXP_SUBSTR(REGEXP_SUBSTR('Cust1234567B', '(?:^|\D)\d{7}(?:\D|$)'), '\d{7}') to just pull out the 7 digits. Is there a way to exclude returning the first and last non-digit characters if they exist in the original? – Laurence Jun 11 '13 at 23:32
  • @Laurence: it would be if oracle supported `?:` which you as you remember we ought to remove – zerkms Jun 11 '13 at 23:51
4

I faced the same problem, what I did was simply

NOT regexp_like(<pattern_you_want_to_negate>)
James H
  • 531
  • 6
  • 15