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

[Anonymous]

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? 

[Anonymous · Accepted solution]

Hi,

Please notice that you should avoid using special characters in the item names including:

"~", "!", "`", "#", "^", "&", "*", "\\", "/", "?", ":", ";", "\"", ".", "(", ")", "'", "¥", "'", """, "«", "»", "<", ">".

These characters are all replaced with "_" in the file system and you may end up creating duplicate item

Best regards

Sabrina

[gunjan_pant]

Hi,

It would be helpful if someone could post the correct regex to be used in the data profiling