Five Stars

Java regex working in tMap does not work in Regex pattern in Profiling.

Hi,

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.

input_S0.SOCIAL_SECURITY_NUMERIC.matches("^(?!\\b(\\d)\\1+(\\d)\\1+(\\d)\\1+\\b)(?!123456789|219099999|078051120)(?!666|000|9\\d{2})\\d{3}(?!00)\\d{2}(?!0{4})\\d{4}$")?"VALID_SSN":"INVALID_SSN"

 

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? 

  • Data Quality
  • Dataprep
  • MDM