Skip to Main Content
  • Questions

  • REGEXP_COUNT and REGEXP_LIKE and the search for a whitespace

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Marian.

Asked: January 14, 2025 - 1:32 pm UTC

Last updated: January 20, 2025 - 8:36 am UTC

Version: 19.25

Viewed 100+ times

You Asked

Hello,

As far as I understand it, Oracle processes regular expressions according to the POSIX standard, but also supports expressions that originate from Perl.

Currently I had some missleading results when searching for a space. Theoretically, this should be found by the Perl-like expression \s. As I understand it, this is also noted in the Oracle documentation ( https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/regexp.html Table 10-5). However, this does not seem to work in my example. Is this a bug - or is there a reason for this (for me unexpected) result? Should I forget about the Perl expressions and use only the POSIX expressions instead?

Intention: Looking for ORA-01555, followed by a colon, space oder new line.

Unexpected result (expression wasn't found in string)
SELECT 1 AS EXPECTED_RESULT, REGEXP_COUNT ('ORA-01555 caused by SQL statement below', 'ORA-01555[:|\s|\n]') AS REGEXPCNT FROM DUAL;


Expected result if using :space: instead of \s
SELECT 1 AS EXPECTED_RESULT, REGEXP_COUNT ('ORA-01555 caused by SQL statement below', 'ORA-01555[:|[:space:]|\n]') AS REGEXPCNT FROM DUAL;


Best regards,
Marian

and Chris said...

This is as documented. From table 10-3 on the page you linked:

[char...] => Matches any single character in the list within the brackets. In the list, all operators except these are treated as literals:

Range operator: -
POSIX character class: [: :]
POSIX collation element: [. .]
POSIX character equivalence class: [= =]


So the regex:

[:|\s|\n]

Matches colon, pipe, backslash, s, and n.

To use Perl expressions, use paratheses instead of square brackets:

SELECT 1 AS EXPECTED_RESULT, 
  REGEXP_COUNT (
    'ORA-01555 caused by SQL statement below', 
    'ORA-01555(:|\s|\n)'
  ) AS REGEXPCNT 
FROM DUAL;

EXPECTED_RESULT  REGEXPCNT
--------------- ----------
              1          1


Rating

  (1 rating)

Comments

Perl influenced in Oracle regexp

mathguy, January 16, 2025 - 5:34 pm UTC

The big point here is that in a matching character class, most of the meta characters lose their special meaning - not just the escape operator. Chris already showed the right way to do what you attempted - use alternation (in parentheses), not matching character classes.

Aside from that, please note that Oracle doesn't support \n. This applies to Chris's solution too, his expression will also match a literal n (the backslash will just be ignored). Also, \s which is the same as [:space:] does NOT mean "space", but "any whitespace character" ( including both "space" and "newline" but also tab, formfeed, carriage return). If that is what you actually need, just use \s, no need to figure out how to deal specifically with \n.
Chris Saxon
January 20, 2025 - 8:36 am UTC

Good point mathguy, thanks for the correction
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.