Password Validation Using Regular Expression In Teradata 14 & 15

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. 🙂

2 thoughts on “Password Validation Using Regular Expression In Teradata 14 & 15

  1. 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.

    1. 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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s