Re: Regexp like

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 4 Feb 2010 11:32:03 -0800
Message-ID: <bf46381002041132k7c7474b7gfccc5f1ad473507c_at_mail.gmail.com>

On Thu, Feb 4, 2010 at 9:23 AM, Joe Smith <joe_dba_at_hotmail.com> 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:

_at_pwd = qw(Thisisatest Passw0rd_ Thisshouldfail Thisshouldw0rk#_);

foreach $pwd (_at_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

_at_pwd = qw(Thisisatest Passw0rd_ Thisshouldfail Thisshouldw0rk#_);

foreach $pwd (_at_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_at_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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 04 2010 - 13:32:03 CST