Today, we’ll be checking one new area where we can implement regular expression to achieve the password validation without involving any kind of Macro, Stored-Proc.
Let’s consider the following conditions to be implemented –
1. Password should contain characters between 6 & 10.
2. One character should be digit.
3. One character should be in upper case letter.
4. There should be at least one special character.
Let’s check the Query & Output –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
select seq_no,
passwd,
regexp_similar(passwd,'^(?=^([[:graph:]]{6,10})$)(?=.*([[:upper:]]{1,}))(?=.*([[:digit:]]{1,})).*$') as reg_test
from scott.login_det
order by 1;
SEQ_NO PASSWD REG_TEST
----- ------- --------------
1 hoti 0
2 hotimla 0
3 hotImla 0
4 hot@imla 0
5 hoT@imla 0
6 hoT@iml9a 1
7 hoT@iml9a66 0
|
Similarly, you can add condition of lower case character if you want to make it more complex.
Hope, this will give you another way – to implement the same logic. 🙂
To make the “REGEXP_SIMILAR()” function work, I had to add the match_arg argument. I chose the ‘c’ = case sensitive matching option.
Without the third argument, my instance of Teradata returns this error: Function “REGEXP_SIMILAR” called with an invalid number or type of parameters.
SELECT
REGEXP_SIMILAR(‘hoT@iml9a’, ‘^(?=^([[:graph:]]{6,10})$)(?=.*([[:upper:]]{1,}))(?=.*([[:digit:]]{1,})).*$’, ‘C’ ) AS reg_test
;
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/SQL_Reference/B035_1145_111A/Regular_Expr_Functions.085.30.html#ww14956646
Thank you for these blog posts. REGEX is very cool.
Perhaps, they have removed this dependency in TD 15. All these latest query that I ran in TD 15.0.
Which version are you using now?
Can you try that in TD 15 as well? I think it should work.
You can download the latest version from Teradata site.