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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

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

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
1 REPLY
Moderator

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

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.