Password Validation Using Regular Expression In Teradata 14 & 15

Posted on by SatyakiDe in 14, friends, function, pattern matching, regexp_replace, regexp_similar, regexp_substr, regular expression, sql, String Manipulation, table, Teradata

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

Published by SatyakiDe

I love to follow all the latest tech shows or technological programs, space science across the world, and so on. Love to watch Suspense thrillers and wants to have spicy food. That's a slice of me. :)

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.