_dexter@FAKE> create table pass(passwd varchar2(200)) ;Table created._dexter@FAKE> insert into pass values ('1') ;1 row created._dexter@FAKE> insert into pass values ('d') ;1 row created._dexter@FAKE> insert into pass values ('11111') ;1 row created._dexter@FAKE> insert into pass values ('111111') ;1 row created._dexter@FAKE> insert into pass values ('dddddd') ;1 row created._dexter@FAKE> insert into pass values ('dddddddddd') ;1 row created._dexter@FAKE> insert into pass values ('1111111111') ;1 row created._dexter@FAKE> insert into pass values ('d1d') ;1 row created._dexter@FAKE> insert into pass values ('1d1') ;1 row created._dexter@FAKE> insert into pass values ('111111111d') ;1 row created._dexter@FAKE> insert into pass values ('dddddddddddd1') ;1 row created._dexter@FAKE> commit ;Commit complete._dexter@FAKE> select * from pass ;PASSWD ------------------------------ 1 d 11111 111111 dddddd dddddddddd 1111111111 d1d 1d1 111111111d dddddddddddd111 rows selected. select passwd from pass where regexp_like(passwd,'(^[1-9]+[a-zA-Z]+|^[a-zA-Z]+[1-9]+)') and length(passwd)>=6 _dexter@FAKE> /PASSWD ------------------------------ 111111111d dddddddddddd1_dexter@FAKE> --只用一个regexp_like也能实现,需要分四种情况,感觉比较复杂就没有写,坐等高手
with t1 as ( select 'aab1b' c1 from dual union all select '4aa%4fs' c1 from dual union all select 'fffa1ffff' c1 from dual union all select 'ff33443' c1 from dual union all select '322@3432' c1 from dual union all select 'aaaa44b' c1 from dual union all select 'sd^f334' c1 from dual )select * from t1 where regexp_like(c1,'[a-zA-Z0-9]+{6,}') and regexp_replace(c1,'[0-9]','') is not null and regexp_replace(c1,'[a-zA-Z]','') is not null ; c1 -------------------- 1 fffa1ffff 2 ff33443 3 aaaa44b
select passwd from pass where regexp_like(passwd,'(^[1-9]+[a-zA-Z]+|^[a-zA-Z]+[1-9]+)$') and length(passwd)>=6--疏忽了
_dexter@FAKE> select * from pass ;PASSWD ------------------------------ 1 d 11111 111111 dddddd dddddddddd 1111111111 d1d 1d1 111111111d dddddddddddd1 %$$%#@ddddd1 %$$%#@ddddd1 11111%$$%#@ddddd1 11111%$$%#@ddddd1&&&&&& 11111d%$$%#@ddddd1&&&&&&16 rows selected._dexter@FAKE> select * 2 from pass 3 where regexp_like(passwd,'[a-zA-Z0-9]+{6,}') 4 and regexp_replace(passwd,'[0-9]','') is not null 5 and regexp_replace(passwd,'[a-zA-Z]','') is not null 6 /PASSWD ------------------------------ 111111111d dddddddddddd1 %$$%#@ddddd1 %$$%#@ddddd1 11111%$$%#@ddddd1 11111%$$%#@ddddd1&&&&&& 11111d%$$%#@ddddd1&&&&&&7 rows selected._dexter@FAKE> select passwd 2 from pass 3 where regexp_like(passwd,'(^[1-9]+[a-zA-Z]+|^[a-zA-Z]+[1-9]+)$') 4 and length(passwd)>=6 5 /PASSWD ------------------------------ 111111111d dddddddddddd1--这东西还是比较难的,坐等正则高手
借用下数据: with t1 as ( select 'aab1b' c1 from dual union all select '4aa%4fs' c1 from dual union all select 'fffa1ffff' c1 from dual union all select 'ff33443' c1 from dual union all select '322@3432' c1 from dual union all select 'aaaa44b' c1 from dual union all select 'sd^f334' c1 from dual )select * from t1 where regexp_replace(c1,'[[:alnum:]]','') is null ;------------ aab1b fffa1ffff ff33443 aaaa44b
掉了开头结尾.. with t1 as ( select '%$$%#@ddddd1' c1 from dual union all select '11111%$$%#@ddddd1' c1 from dual union all select 'fffa1ffff' c1 from dual union all select 'ff33443' c1 from dual union all select '322@3432' c1 from dual union all select '11111d%$$%#@ddddd1&&&&&&' c1 from dual union all select 's1d^f334' c1 from dual )select * from t1 where regexp_like(c1,'^[a-zA-Z0-9]+{6,}$') and regexp_replace(c1,'[0-9]','') is not null and regexp_replace(c1,'[a-zA-Z]','') is not null ; c1 ----------------------- 1 fffa1ffff 2 ff33443
select * from t1 where regexp_replace(c1,'[[:alnum:]]','') is null and length(c1) >= 6 ;少了判断长度呵呵
哦 我疏忽了 sorry select * from t1 where regexp_replace(c1,'[[:alnum:]]','') is null and regexp_replace(c1,'[[:alpha:]]','') is not null and regexp_replace(c1,'[[:digit:]]','') is not null and length(c1) >= 6 ;
找到答案了(P_PASSW值为密码的一个变量) SELECT COUNT(1) INTO FLAG FROM DUAL where REGEXP_LIKE(P_PASSW, '[[:alpha:]]') AND REGEXP_LIKE(P_PASSW, '[[:digit:]]') AND length(P_PASSW) >= 6; IF FLAG = 0 THEN BEGIN P_IS_SUCCESS := 0; P_MSG := '密码格式错误。'; RETURN; END; END IF; 请大家帮忙看看我这个如何?
_dexter@FAKE> create table pass(passwd varchar2(200)) ;Table created._dexter@FAKE> insert into pass values ('1') ;1 row created._dexter@FAKE> insert into pass values ('d') ;1 row created._dexter@FAKE> insert into pass values ('11111') ;1 row created._dexter@FAKE> insert into pass values ('111111') ;1 row created._dexter@FAKE> insert into pass values ('dddddd') ;1 row created._dexter@FAKE> insert into pass values ('dddddddddd') ;1 row created._dexter@FAKE> insert into pass values ('1111111111') ;1 row created._dexter@FAKE> insert into pass values ('d1d') ;1 row created._dexter@FAKE> insert into pass values ('1d1') ;1 row created._dexter@FAKE> insert into pass values ('111111111d') ;1 row created._dexter@FAKE> insert into pass values ('dddddddddddd1') ;1 row created._dexter@FAKE> commit ;Commit complete._dexter@FAKE> select * from pass ;PASSWD
------------------------------
1
d
11111
111111
dddddd
dddddddddd
1111111111
d1d
1d1
111111111d
dddddddddddd111 rows selected. select passwd
from pass
where regexp_like(passwd,'(^[1-9]+[a-zA-Z]+|^[a-zA-Z]+[1-9]+)')
and length(passwd)>=6
_dexter@FAKE> /PASSWD
------------------------------
111111111d
dddddddddddd1_dexter@FAKE>
--只用一个regexp_like也能实现,需要分四种情况,感觉比较复杂就没有写,坐等高手
with t1 as
(
select 'aab1b' c1 from dual
union all
select '4aa%4fs' c1 from dual
union all
select 'fffa1ffff' c1 from dual
union all
select 'ff33443' c1 from dual
union all
select '322@3432' c1 from dual
union all
select 'aaaa44b' c1 from dual
union all
select 'sd^f334' c1 from dual
)select *
from t1
where regexp_like(c1,'[a-zA-Z0-9]+{6,}')
and regexp_replace(c1,'[0-9]','') is not null
and regexp_replace(c1,'[a-zA-Z]','') is not null ;
c1
--------------------
1 fffa1ffff
2 ff33443
3 aaaa44b
select passwd
from pass
where regexp_like(passwd,'(^[1-9]+[a-zA-Z]+|^[a-zA-Z]+[1-9]+)$')
and length(passwd)>=6--疏忽了
_dexter@FAKE> select * from pass ;PASSWD
------------------------------
1
d
11111
111111
dddddd
dddddddddd
1111111111
d1d
1d1
111111111d
dddddddddddd1
%$$%#@ddddd1
%$$%#@ddddd1
11111%$$%#@ddddd1
11111%$$%#@ddddd1&&&&&&
11111d%$$%#@ddddd1&&&&&&16 rows selected._dexter@FAKE> select *
2 from pass
3 where regexp_like(passwd,'[a-zA-Z0-9]+{6,}')
4 and regexp_replace(passwd,'[0-9]','') is not null
5 and regexp_replace(passwd,'[a-zA-Z]','') is not null
6 /PASSWD
------------------------------
111111111d
dddddddddddd1
%$$%#@ddddd1
%$$%#@ddddd1
11111%$$%#@ddddd1
11111%$$%#@ddddd1&&&&&&
11111d%$$%#@ddddd1&&&&&&7 rows selected._dexter@FAKE> select passwd
2 from pass
3 where regexp_like(passwd,'(^[1-9]+[a-zA-Z]+|^[a-zA-Z]+[1-9]+)$')
4 and length(passwd)>=6
5 /PASSWD
------------------------------
111111111d
dddddddddddd1--这东西还是比较难的,坐等正则高手
with t1 as
(
select 'aab1b' c1 from dual
union all
select '4aa%4fs' c1 from dual
union all
select 'fffa1ffff' c1 from dual
union all
select 'ff33443' c1 from dual
union all
select '322@3432' c1 from dual
union all
select 'aaaa44b' c1 from dual
union all
select 'sd^f334' c1 from dual
)select *
from t1
where regexp_replace(c1,'[[:alnum:]]','') is null
;------------
aab1b
fffa1ffff
ff33443
aaaa44b
with t1 as
(
select '%$$%#@ddddd1' c1 from dual
union all
select '11111%$$%#@ddddd1' c1 from dual
union all
select 'fffa1ffff' c1 from dual
union all
select 'ff33443' c1 from dual
union all
select '322@3432' c1 from dual
union all
select '11111d%$$%#@ddddd1&&&&&&' c1 from dual
union all
select 's1d^f334' c1 from dual
)select *
from t1
where regexp_like(c1,'^[a-zA-Z0-9]+{6,}$')
and regexp_replace(c1,'[0-9]','') is not null
and regexp_replace(c1,'[a-zA-Z]','') is not null ; c1
-----------------------
1 fffa1ffff
2 ff33443
from t1
where regexp_replace(c1,'[[:alnum:]]','') is null
and length(c1) >= 6
;少了判断长度呵呵
select *
from t1
where regexp_replace(c1,'[[:alnum:]]','') is null
and regexp_replace(c1,'[[:alpha:]]','') is not null
and regexp_replace(c1,'[[:digit:]]','') is not null
and length(c1) >= 6
;
SELECT COUNT(1)
INTO FLAG
FROM DUAL
where REGEXP_LIKE(P_PASSW, '[[:alpha:]]')
AND REGEXP_LIKE(P_PASSW, '[[:digit:]]')
AND length(P_PASSW) >= 6;
IF FLAG = 0 THEN
BEGIN
P_IS_SUCCESS := 0;
P_MSG := '密码格式错误。';
RETURN;
END;
END IF;
请大家帮忙看看我这个如何?