Search code examples
regexoracle-databaseplsqloracle11g

Why does this Oracle regexp_like test FALSE?

Why does this Oracle regexp_like expression test FALSE? I'm running this PLSQL on Oracle 11g.

v_string is a 0 and two spaces. It tests TRUE at REGEX101

Oracle regexp_like

 declare  v_string varchar2(30) := '0  ';

 begin    
 dbms_output.put_line('Length is ' || length(v_string) );   

 if  regexp_like(v_string,'^[0-9A-Z\s]{1,3}$') then   
      dbms_output.put_line('TRUE');    
 else    
      dbms_output.put_line('FALSE');    
 end if;   

 end;
Solution

  • You should not use a shorthand character class inside a bracket expression since it is treated as 2 separate characters, \ and s. Use an equivalent [:space:] POSIX character class:

    ^[0-9A-Z[:space:]]{1,3}$
        ^^^^^^^^^

    Note that

    Shorthand character class and other
    common PCRE-like variations    		 Oracle POSIX version
    \d [:digit:] (any digit)
    \s [:space:] (all) / [:blank:] (horizontal only)
    \w [:alnum:]_ (\w matches letters, digits and underscores in most flavors)
    [\p{P}\p{S}] [:punct:] (any punctuation and math symbols)
    \p{L} [:alpha:] (any letter)
    \p{Cc} [:cntrl:] (control characters)
    [\d\p{L}\p{P}\p{S}] [:graph:] (any punctuation, letter and digit symbols)
    \p{Ll} [:lower:] (lowercase letters)
    \p{Lu} [:upper:] (uppercase letters)
    [a-fA-F0-9] [:xdigit:] (hexadecimal number digits)

    NOTE: When using POSIX character classes, ALWAYS use them inside bracket expressions:

    Cf. KEYWORD_([:alnum:]+) and KEYWORD_([[:alnum:]]+) (or - to fully emulate \w - KEYWORD_([[:alnum:]_]+), with the underscore).