Re: Regexp like

From: Jared Still <jkstill@xxxxxxxxx>
To: joe_dba@xxxxxxxxxxx
Date: Thu, 4 Feb 2010 11:32:03 -0800
On Thu, Feb 4, 2010 at 9:23 AM, Joe Smith <joe_dba@xxxxxxxxxxx> wrote:

> Has anybody used REGEXP_LIKE to validate passwords?
>
> I have a table with strings (i.e. passwords) but this select is not
> returning  any values.
>
> select * from dept
> where REGEXP_LIKE(LOC,'^.*(?=.*{9,})(?=.*\d)(?=.*[A-Za-z])(?=.*[_#]).*$'));
>
>

That RE is not quite valid:

There is a double quantifier -  ?=.*{9,}
Change it to ?=.{9,} and it works in Perl

Here's a Perl snippet:

@pwd = qw(Thisisatest Passw0rd_ Thisshouldfail Thisshouldw0rk#_);

foreach $pwd (@pwd) {
        print "Password: $pwd - ";
        if ($pwd =~ /^.*(?=.{9,})(?=.*\d)(?=.*[A-Za-z])(?=.*[_#]).*$/)
        {print "passed\n" }
        else {print "FAILED\n"}
}

$>  perl pwdre_joe.pl
Password: Thisisatest - FAILED
Password: Passw0rd_ - passed
Password: Thisshouldfail - FAILED
Password: Thisshouldw0rk#_ - passed

Here's another RE in Perl

@pwd = qw(Thisisatest Passw0rd_ Thisshouldfail Thisshouldw0rk#_);

foreach $pwd (@pwd) {
        print "Password: $pwd - ";
        if ($pwd =~
/^(?=[-_#a-zA-Z0-9]*?[A-Z])(?=[-_#a-zA-Z0-9]*?[a-z])(?=[-_#a-zA-Z0-9]*?[0-9])[-_#a-zA-Z0-9]{9,}$/)
        {print "passed\n" }
        else {print "FAILED\n"}
}

$> perl pwdre.pl
Password: Thisisatest - FAILED
Password: Passw0rd_ - passed
Password: Thisshouldfail - FAILED
Password: Thisshouldw0rk#_ - passed

Neither of these however work in Oracle 10gR2 or 11gR1

  1  select pwd
  2  from (
  3     select 'Thisisatest' pwd from dual
  4     union all
  5     select 'Passw0rd_' pwd from dual
  6     union all
  7     select 'Thisshouldfail' pwd from dual
  8     union all
  9     select 'Thisshouldw0rk#-' pwd from dual
 10  )
 11  where
REGEXP_LIKE(pwd,'^.*(?=.{9,})(?=.*\d)(?=.*[A-Za-z])(?=.*[_#]).*$/')
 12* --where
REGEXP_LIKE(pwd,'^(?=[-_#a-zA-Z0-9]*?[A-Z])(?=[-_#a-zA-Z0-9]*?[a-z])(?=[-_#a-zA-Z0-9]*?[0-9])[-_#a-zA-Z0-9]{9,}$')
11:29:14 ordevdb01.radisys.com - js001292@dv11 SQL> /

no rows selected

This would likely take some digging into the Oracle Regex Documentation.

Their implementation of RE differs somehow.


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
References:
- Regexp like
  - From: Joe Smith
Re: Regexp like

Other related posts:
