Oracle - why do none of the provided regexs work?

user_15124130 0
Viewed 47

I have tried multiple regexs from here and none of them work correctly. Example:

declare
  v_result integer;
  v_link nvarchar2(300) := 'http://example.com/blah_blah/';
  v_regexp nvarchar2(300) := '@^(https?|ftp)://[^\s/$.?#].[^\s]*$@iS';
begin
  begin
    select 1 into v_result
    from dual
    where regexp_like(v_link, v_regexp);
  exception when no_data_found then
      dbms_output.put_line('false');
      return;
  end;
  dbms_output.put_line('true');
end;

This returns false, although it should return true. Why?

plsql oracle regex
2 Answers

Your regex pattern appears to be taken from some source which requires formal delimiters in the pattern. This is not required in Oracle, and in fact your leading/trailing @ will match this literal character. Also, if you want to use REGEXP_LIKE in case insensitive mode, you should pass 'i' as the third parameter flag, rather than using @i appended to the end of the pattern.

Also note that the pattern itself had problems. You probably intended the first character class to be +, one or more. And also, the literal dot separating the domain from the ending needs to be escaped with backslash. Finally, if you want to express whitespace inside a character class, you should be using [:space:], not \s, the latter which will be interpreted as literal character s. Taking all this into account, consider this version:

SELECT *
FROM yourTable
WHERE REGEXP_LIKE(url, '^(https?|ftp)://[^[:space:]/$.?#]+\.[^[:space:]]*$', 'i');

Data:

WITH yourTable AS (
    SELECT 'http://example.com/blah_blah/' AS url FROM dual UNION ALL
    SELECT 'http://test.com' FROM dual
)

Demo

user_18632290

Regular expressions in Oracle do not support:

  • Characters prefixing the regular expression (many of the examples on the page you link start with @, #, ~ or _). Similarly, Oracle does not support the same characters as suffixes. These appear to be specific to certain parsers/languages and are not standard regular expression syntax.
  • Flags (i.e. case-insensitivity) embedded at the beginning or end of the regular expression (many examples end with @iS or start with (?i)). This would need to be passed as a parameter to the regular expression matching function you are using rather than being embedded in the regualr expression string.
  • Perl-like patterns inside character set matches (In Oracle, [^\s] would match any character that is not \ or s when the intent is to match any character that is not a space character matched by the Perl-style \s expression; this needs to be translated to the POSIX expression [^[:space:]]).
  • Non-capturing groups (Oracle does not support (?: ) and would need to be converted to a capturing group ( )).
  • Word boundaries (Oracle does not support \b. There is no replacement syntax that can be used; you just need to remove these from the regular expression).
  • Look ahead (Oracle does not support (?= ). Again, there is no replacement for this syntax; you either need to find an equivalent construct without look-ahead or cannot use the regular expressions that rely on this).
  • Escaped - or ] characters in character set matches. (In Oracle, you would need to use []a-z-] with the ] character as the first character in the character set and the - as the last character in the character set rather than using [a-z\-\]] where \ is used to escape those characters).

There may be more that is not supported but you would need to correct all these issues to fix the regular expressions.

user_15092640
Related