2

I have a simple SOQL query of the Contact standard object:

https://myinstanceurl.sandbox.my.salesforce.com/services/data/v54.0/query?q=SELECT+FirstName,LastName,MiddleName,Suffix+FROM+Contact+WHERE+FirstName+=+'Barry'

This returns

{
    "totalSize": 1046,
    "done": true,
    "records": [
        {
            "attributes": {
                "type": "Contact",
                "url": "/services/data/v54.0/sobjects/Contact/0033a00..."
            },
            "FirstName": "Barry",
            "LastName": "Cockatoo",
            "MiddleName": null,
            "Suffix": null
        },
        {
            "attributes": {
                "type": "Contact",
                "url": "/services/data/v54.0/sobjects/Contact/0033a000..."
            },
            "FirstName": "Barry",
            "LastName": "Rooster",
            "MiddleName": null,
            "Suffix": null
        },

...
... (etc)

I try the same search, but with lowercase barry:

https://myinstanceurl.sandbox.my.salesforce.com/services/data/v54.0/query?q=SELECT+FirstName,LastName,MiddleName,Suffix+FROM+Contact+WHERE+FirstName+=+'barry'

This returns:

{
    "totalSize": 1,
    "done": true,
    "records": [
        {
            "attributes": {
                "type": "Contact",
                "url": "/services/data/v54.0/sobjects/Contact/003j00..."
            },
            "FirstName": "barry",
            "LastName": "Peacock",
            "MiddleName": null,
            "Suffix": null
        }
    ]
}

This search seems to clearly be done in a case-sensitive manner. However, all the documentation and message board posts I can find say that search is mostly case-insensitive:

I can also describe the Contact.FirstName field with the following query:

https://myinstanceurl.my.salesforce.com/services/data/v54.0/sobjects/contact/describe

In the field description for FirstName, I can see:

{
            "aggregatable": false,
            "aiPredictionField": false,
            "autoNumber": false,
            "byteLength": 120,
            "calculated": false,
            "calculatedFormula": null,
            "cascadeDelete": false,
            "caseSensitive": false,
            "compoundFieldName": "Name",
            "controllerName": null,
            "createable": true,
            "custom": false,
            "defaultValue": null,
            "defaultValueFormula": null,
            "defaultedOnCreate": false,
            "dependentPicklist": false,
            "deprecatedAndHidden": false,
            "digits": 0,
            "displayLocationInDecimal": false,
            "encrypted": true,
            "externalId": false,
            "extraTypeInfo": "personname",
            "filterable": true,
            "filteredLookupInfo": null,
            "formulaTreatNullNumberAsZero": false,
            "groupable": false,
            "highScaleNumber": false,
            "htmlFormatted": false,
            "idLookup": false,
            "inlineHelpText": null,
            "label": "First Name",
            "length": 40,
            "mask": null,
            "maskType": null,
            "name": "FirstName",
            "nameField": false,
            "namePointing": false,
            "nillable": true,
            "permissionable": false,
            "picklistValues": [],
            "polymorphicForeignKey": false,
            "precision": 0,
            "queryByDistance": false,
            "referenceTargetField": null,
            "referenceTo": [],
            "relationshipName": null,
            "relationshipOrder": null,
            "restrictedDelete": false,
            "restrictedPicklist": false,
            "scale": 0,
            "searchPrefilterable": false,
            "soapType": "xsd:string",
            "sortable": false,
            "type": "string",
            "unique": false,
            "updateable": true,
            "writeRequiresMasterRead": false
        },

It does not appear to be caseSensitive. In fact, all of the fields on the Contact object are marked caseSensitive: false. And yet, they all appear to query in a case-sensitive manner...

What am I missing here? Any help is appreciated!

2
  • 3
    is Contact.FirstName encrypted? (with case sensitive encryption)?
    – cropredy
    Dec 3, 2022 at 0:09
  • @cropredy--yep, this was the reason! Dec 5, 2022 at 14:04

2 Answers 2

2

This was because the field is encrypted. See this post: SOQL - Query for Last Name of Contacts Also, I can see in the field description for FirstName that it is "encrypted": true

0

I think you're confusing the searching engine/process, with the field definition.

The describe result for Contact.FirstName that you've shown is providing information about the field. The caseSensitive there works with the unique attribute to determine whether a field is unique in either a case sensitive or case insensitive way.

1
  • Thanks. Perhaps I am misunderstanding the documentation here. How, then, should I interpret this description of the '=' comparison operator: "Expression is true if the value in the fieldName equals the value in the expression. String comparisons using the equals operator are case-sensitive for unique case-sensitive fields and case-insensitive for all other fields." Does this mean that FirstName = 'Steve' is the same as firstname = 'Steve'? And has absolutely nothing to do with FirstName = 'Steve' vs FirstName = 'steve' And if so... how can one actually implement case-insens. search? Dec 2, 2022 at 23:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .