Salesforce SOQL String Functions not working (LEFT, SUBSTITUTE etc)

0
votes

I'm trying to get a list of domain names from email addresses using the Salesforce query language. This potentially really simple and something I would normally accomplish with split_part in postgresql, like:

SELECT split_part(Email, '@', 2)
FROM Lead
GROUP BY 1

I've been digging through the SOQL documentation and can't really find any standard string functions. However, there's this salesforce community answer: https://success.salesforce.com/answers?id=90630000000gi8EAAQ which uses LEFT, FIND and SUBSTITUTE. I tried something as simple as:

SELECT LEFT(Email, 3) FROM Lead limit 10

But get:

Error: MALFORMED_QUERY
Message: SELECT LEFT(Email, 3) FROM Lead limit 10
ERROR at Row:1:Column:17\nunexpected token: ','

Have these functions been deprecated?

I have lots of potential domain names and don't really want to query for pages and pages of every possible email address or I'll quickly hit my Salesforce API limit.

1
salesforcesoql

1 Answers

3
votes

Those functions are not available in SOQL/SOSL. The link you provided refers to Custom Formula Fields. Are you trying to get the data using the UI or the API? Without knowing how you are extracting the data, the following are general suggestions:

  1. You can create a formula field on your object called Email_Domain__c. Then, you can query or filter by that field.

  2. You can also use custom formulas in Reports to filter your results.

  3. Use Apex and/or Visualforce to extract/display/export the data.

  4. Use the LIKE keyword to filter by domain name as in:

SELECT Email FROM LEAD WHERE Email LIKE '%gmail.com%'

The LIKE keyword is not efficient. https://trailhead.salesforce.com/en/modules/database_basics_dotnet/units/writing_efficient_queries