What functions are accepted in the Advanced Data Filter?
The LENGTH function returns the number of characters in a string including trailing blanks. The syntax is LENGTH(string). You can enter any valid expression as the string.
You can use almost any Informatica function in an Advanced Data Filter. All of the Informatica functions are described in the documentation and online help.
We currently offer a list of functions with syntax help in our Field Expression dialog, but unfortunately we do not have a similar list of functions in our Advanced Data Filter dialog. There is already an idea for this enhancement on the Community: https://community.informatica.com/ideas/1012. If you vote this idea up, it will be more likely to be implemented in a future release of Informatica Cloud.
Josh, Thanks for your repsonse. However, when I try and use this function in the data filter:
Length(MailingPostalCode) = 5, I get this error:
[FATAL] Query failed. User [email@example.com]. SOSQL [Select Id, Mailing_County__c, MailingCity, MailingPostalCode, MailingState, MailingStreet From Contact Where ((LENGTH(MailingPostalCode) = 5))]. Fault code [sf:MALFORMED_QUERY]. Reason [MALFORMED_QUERY: Invalid aggregate function: LENGTH].
I'm assuming this is a Data Synchronization task with a Salesforce source. Is that correct?
Yes, that is correct.
After some further research, it looks like I was totally wrong about the Advanced Data Filters. My apologies!
Advanced Data Filters cannot include Informatica functions like LENGTH. They can only include functions that are supported by the query language of the source connection, which in your case is Salesforce.
If your source connection used the SQL query language then you could use the LEN function. But the Salesforce query language is SOQL, and unfortunately it looks like SOQL doesn't have a length function.
Sorry that I couldn't be more helpful. Maybe someone else on the community has found another solution to this problem.