regexoracle-databaseplsqlregexp-like

Regex that allows spaces, hyphens, uppercase and digits in PLSQL

I'm learning about regular expressions. This expression doesn't work in PLSQL:

^[A-Z0-9 \-]{4,12}$

I'm using it this way:

IF C_INFO.ID IS NOT NULL AND REGEXP_LIKE(C_INFO.ID,'^[A-Z0-9 \-]{4,12}$') 
THEN...

What am I doing wrong? It must allow uppercase, digits, spaces, hyphens and between 4 and 12 characters.

It fails when ID = 'aaaa', for some reason it doesn't seem to be case sensitive. Do you think the expression is right?

Thanks in advance! E.

Solution

  • Oh well, it is about database version, I think. Which version do you use?

    regexp_like should have the 'c' match parameter which specifies case-sensitive matching.

    A few examples:

    10g is OK

    SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> with test as
  2    (select 'ABC13' id from dual union all
  3     select '12-D'     from dual union all
  4     select 'A\2'      from dual union all
  5     select 'A5 \ 3-2' from dual union all
  6     select 'aaaa'     from dual union all
  7     select 'BBBB'     from dual union all
  8     select 'CD 5'     from dual
  9    )
 10  select *
 11  from test
 12  where regexp_like (id, '[A-Z0-9 \-]{4,12}', 'c');

ID
--------
ABC13
12-D
A5 \ 3-2
BBBB
CD 5

SQL>

    11g is OK

    SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> with test as
  2    (select 'ABC13' id from dual union all
  3     select '12-D'     from dual union all
  4     select 'A\2'      from dual union all
  5     select 'A5 \ 3-2' from dual union all
  6     select 'aaaa'     from dual union all
  7     select 'BBBB'     from dual union all
  8     select 'CD 5'     from dual
  9    )
 10  select *
 11  from test
 12  where regexp_like (id, '[A-Z0-9 \-]{4,12}', 'c');

ID
--------
ABC13
12-D
A5 \ 3-2
BBBB
CD 5

SQL>

    18cXE is not OK

    SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> with test as
  2    (select 'ABC13' id from dual union all
  3     select '12-D'     from dual union all
  4     select 'A\2'      from dual union all
  5     select 'A5 \ 3-2' from dual union all
  6     select 'aaaa'     from dual union all
  7     select 'BBBB'     from dual union all
  8     select 'CD 5'     from dual
  9    )
 10  select *
 11  from test
 12  where regexp_like (id, '[A-Z0-9 \-]{4,12}', 'c');

ID
--------
ABC13
12-D
A5 \ 3-2
aaaa                       --> this shouldn't be here
BBBB
CD 5

6 rows selected.

SQL>

    19cEE is OK

    (screenshot from apex.oracle.com, as I don't have that database available elsewhere)

    enter image description here

    So, how to make it work, regardless database version? Add yet another where condition, e.g.

    SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> with test as
  2    (select 'ABC13' id from dual union all
  3     select '12-D'     from dual union all
  4     select 'A\2'      from dual union all
  5     select 'A5 \ 3-2' from dual union all
  6     select 'aaaa'     from dual union all
  7     select 'BBBB'     from dual union all
  8     select 'CD 5'     from dual
  9    )
 10  select *
 11  from test
 12  where regexp_like (id, '[A-Z0-9 \-]{4,12}', 'c')
 13    and not regexp_like(id, '[[:lower:]]');

ID
--------
ABC13
12-D
A5 \ 3-2
BBBB
CD 5

SQL>