SQL & PL/SQL

About password verification routine.

Nicolas.

Hi Nicholas

I'm very comfortable with doing it the long way, ie

isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN

BUT, I can do this with one line using a regexp_like operator if someone can see what the problem is with my regexp code please?

Phil

HI Phil,

Check this out:

WITH data_Set AS
(
SELECT 1 row_no, '3434HERE' text_col FROM dual
UNION ALL
SELECT 2 row_no, 'EYRERY787' text_col FROM dual
UNION ALL
SELECT 3 row_no, 'DHFD67SDSD' text_col FROM dual
UNION ALL
SELECT 4 row_no, 'ERERT' text_col FROM dual
UNION ALL
SELECT 4 row_no, '23232' text_col FROM dual
)
SELECT row_no FROM data_set
WHERE REGEXP_LIKE(UPPER('67'),'([[:alpha:]]+[[:digit:]]+[[:alpha:]]+)|([[:digit:]]+[[:alpha:]]+)|([[:alpha:]]+[[:digit:]]+)')
/

Result as follows:
ROW_NO
----------
1
2
3

Regards,

Raj
www.oraclebrains.com

Rajs

You left a typo in there (the 67) but I've fixed it and the following meets the requirement. It should be possible to further refine it to one regexp if someone is familiar with this, otherwise I'll probably use this

WITH data_Set AS
(
SELECT 1 row_no, '3434HERE' text_col
FROM dual
UNION ALL
SELECT 2 row_no, 'EYRERY787' text_col
FROM dual
UNION ALL
SELECT 3 row_no, 'DHFD67SDSD' text_col
FROM dual
UNION ALL
SELECT 4 row_no, 'ERERT' text_col
FROM dual
UNION ALL
SELECT 4 row_no, '23232' text_col
FROM dual)
SELECT *
FROM data_set
WHERE REGEXP_LIKE(UPPER(text_col), '[[:alpha:]]{4,8}')
and REGEXP_LIKE(UPPER(text_col), '[[:digit:]]{1,7}')
and length(text_col) between 4 and 8

UPPER is redundant in your query.

You have an issue, because according to your conditiotns you can't have more than 4 digits in your password (because you want to have alpha{4,8} and the length of password is between 4 and 8).

So, please - provide more detailed description of what kind of password can you have.
For example can you have a password like AA11AA11
according to your query - it is wrong. Is it really so?

An other way :

select *
from   data_set
where  length(regexp_replace(text_col, '[[:alpha:]]')) between 1 and 7 --number of digit
and    length(regexp_replace(text_col, '[[:digit:]]')) between 4 and 8 --number of alpha
and    length(text_col) between 4 and 8 ;

You can avoid the UPPER function.
Furthermore, you cannot have 8 alpha max and 1 digit min since the max length is 8. And the min length 4 can never be done since the min alpha is 4 and the min digit is 1, so the min is 5.

Nicolas.

WHERE REGEXP_LIKE(UPPER(text_col), '[[:alpha:]]{4,8}')
and REGEXP_LIKE(UPPER(text_col), '[[:digit:]]{1,7}')
and length(text_col) between 4 and 8

Is fine thanks - OK, the numeric should really be 1,4 and alpha 4,7 but this covers a simple requirement of password between 4 and 8 characters and including 1 (to 4) numbers.

Thanks

Phil

Here are 4 digits and 4 alphas is it wrong or correct password?

SQL> WITH data_Set AS(
  2  SELECT 1 row_no, '3434HERE' text_col FROM dual UNION
  3  SELECT 2 row_no, 'EYRERY787' text_col FROM dual UNION
  4  SELECT 3 row_no, 'DHFD67SDSD' text_col FROM dual UNION
  5  SELECT 4 row_no, 'ERERT' text_col FROM dual UNION
  6  SELECT 5 row_no, '23232' text_col FROM dual UNION
  7  SELECT 6 row_no, 'AAA111B' text_col FROM dual UNION
  8  SELECT 7 row_no, 'AA11AA11' text_col FROM dual)
  9  select row_no,text_col,
 10  case when REGEXP_LIKE(text_col,'^([^a-zA-Z]*[a-zA-Z]){4,8}[^a-zA-Z]*$')
 11        and REGEXP_LIKE(text_col,'^([^0-9]*[0-9]){1,7}[^0-9]*$')
 12        and length(text_col) between 4 and 8
 13       then 1 else 0 end as "IsPassWordOK"
 14  from data_Set;

   ROW_NO  TEXT_COL    IsPassWordOK
---------  ----------  ------------
        1  3434HERE               1
        2  EYRERY787              0
        3  DHFD67SDSD             0
        4  ERERT                  0
        5  23232                  0
        6  AAA111B                1
        7  AA11AA11               1

My homepage which is written by Japanese language mentions similar Regex.
http://www.geocities.jp/oraclesqlpuzzle/regex/regex-4-4.html

On Oracle11g,
We can use REGEXP_COUNT.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions132.htm

maybe (not Test)

WITH data_Set AS(
SELECT 1 row_no, '3434HERE' text_col FROM dual UNION
SELECT 2 row_no, 'EYRERY787' text_col FROM dual UNION
SELECT 3 row_no, 'DHFD67SDSD' text_col FROM dual UNION
SELECT 4 row_no, 'ERERT' text_col FROM dual UNION
SELECT 5 row_no, '23232' text_col FROM dual UNION
SELECT 6 row_no, 'AAA111B' text_col FROM dual UNION
SELECT 7 row_no, 'AA11AA11' text_col FROM dual)
select row_no,text_col,
case when REGEXP_COUNT(text_col,'[a-zA-Zbetween 4 and 8
      and REGEXP_COUNT(text_col,'[0-9between 1 and 7
      and length(text_col) between 4 and 8
     then 1 else 0 end as "IsPassWordOK"
from data_Set;

Thanks Phil!