I have few validation rules that i have applied in my talend job. some of these validation rules have been applied in tMap using regex. One example is SSN validation. Following are my requirements :
|Format as XXXXXXXXX|
|Cannot be NULL|
|Can only contain numbers|
|First 3 digits cannot be "666"|
|Cannot be 000-XX-XXX|
|Cannot be XXX-00-XXXX|
|Cannot be XXX-XX-0000|
|First digit cannot be "9"|
|Cannot be 078-05-1120|
|Cannot be 219-09-9999|
|Cannot = "123456789"|
|Cannot contain only one number (i.e. "111111111")|
I am currently using the following regex and it is working fine for all scenarios(checking for null in SchemaCompliance) in tMap.
Now the issue is that i am trying to create a DQ profiling report (Column Analysis) on the SSN column using custom Regex available. In the custom regex, i am using Java regex and used the same regex given above in single quotes as per the documentation. This regex does not work in the custom regex. I am not sure why.
Can someone please help me with an explanation on why this is not working in Custom regex when it works in tMap. If possible, can you also help me with the regex that works?