-1

I am trying to use regexp like to identify numeric pattern XXX XX XXXX within a string i.e numbers in pattern 3 2 4.

Regexp_like(column1, ‘/d{3}/s/d{2}/s/d{4}’)

Where column1 is a varchar or clob column potentially containing the numeric pattern.

But records were the last set of digits is more then 4 are also being pulled up. Is there a way to limit the exact number of digits to check for using regexp_like. Appreciate any suggestions or help.

Even if a number is in pattern 3 2 5 or more then 5 in the last set ..they are being pulled up by my regexp like. I need the exact pattern of 3 2 4 matched in the varchar2 or clob column between aphanumetic and other punctuation marks within the column.

For example data in the column may be something like- ‘This is a pattern test for XXX XX XXXX. So 115 34 45678 should not pull up because last part is 5 digits’.

Please share ideas and or suggestions.

bobble bubble
  • 16,888
  • 3
  • 27
  • 46
Nandu
  • 9
  • Please present the data you are trying to match on - and for good measure, present data that should _not_ match. Do it in table form, not inserted into text. – Ted Lyngmo Nov 25 '22 at 20:16
  • Have a look at [this answer](https://stackoverflow.com/a/52441424/5527985) and try e.g. [`(^|\W)\d{3}\s\d{2}\s\d{4}(\W|$)`](https://regex101.com/r/GhBVH0/2) (the upper `\W` is a short for *non word character*). So this will match if preceded by `^` *start* or `\W` (a character other than a word character) and followed by `$` *end* or `\W`. Most regex flavors support `\b` (*word boundaries*) but seems they don't work here. Further your sample is using forward slashes instead of backslashes. – bobble bubble Nov 25 '22 at 21:05
  • 1
    You want to use \ rather than / in the regular expression and use `^` and `$` to anchor the regular expression to the start- and end-of-the-string. – MT0 Nov 25 '22 at 21:47

1 Answers1

2

Sample data would really help; without it, we (OK, me) have to guess. So, this is my guess:

SQL> with test (col) as
  2    (select 'abc 23 1234'  from dual union all -- nok
  3     select '123 45 6789'  from dual union all --  ok
  4     select '12 345 6789'  from dual union all -- nok
  5     select '123 45 67890' from dual union all -- nok
  6     select '123 a5 67b9'  from dual           -- nok
  7    )
  8  select col
  9  from test
 10  where regexp_like(col, '^\d{3} \d{2} \d{4}$');

COL
------------
123 45 6789

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • The numeric part is within a varchar2 column so the ^ and $ are not the right way to go. In the original question. I have also shown sample varchar2 column data. – Nandu Nov 28 '22 at 17:01