salesforcesoql

SOQL Query - How to write a SOQL query by making a field to lowercase and compare?

Following query returns an error:

Query:

SELECT Id, FirstName, LastName, OwnerId, PersonEmail
FROM Account 
WHERE lower(PersonEmail) = lower('abc.DEF@org.cOM')

API Response:

success: false
    result: Dictionary
    error: IntegrationError
        title: "The JSON body contains an error"
        message: "Salesforce returned the following error code: MALFORMED_QUERY"
        detail: "
'%test%' and lower(PersonEmail) = lower('abc.DEF@org.cOM')
                                 ^
ERROR at Row:4:Column:54
Bind variables only allowed in Apex code"

Can't we use SQL functions in SOQL?

Solution

  • Can't we use SQL functions in SOQL?

    No, you can't. SOQL is a Salesforce-specific dialect. Here's a decent list of what you can use: https://salesforce.stackexchange.com/questions/166372/all-functions-available-in-soql. And any comparison you make must be in field operator value style. You can't compare field value with another field's value (apart from primary / foreign keys... you could write formulas for that though). And you can't do "clever" weird queries WHERE 1=1 AND...

    This is not too different from other SQL dialects really? To me SQL Server's date format "112" is equally strange as to you lack of LOWER. If you really want to have a lowercase value returned/displayed in UI you can make a formula field in SF (bit like adding a column to materialized view?) - but comparisons on it will still be case-insensitive and probably slower, full table search to run ultimately useless function instead of using indexes.

    SOQL is case insensitive on database level (I believe it's called collation?). Any SELECTs you make will return hits ignoring case so you don't have to explicitly call LOWER() There are some exceptions to this but PersonEmail is not one of them: