Wednesday, September 21, 2016

Salesforce SOQL Length function

A typical scenario that I come across for using SOQL functions is for identifying certain type of ExternalIds.
In brief, what happens for large systems over a period of years OR through Mergers & Acquisitions is that, a single identifier field is leveraged to indicate data from multiple systems. Depending on the type of data in this one field, for example, if having 4 digits, means data from system A whereas 12 alphanumeric means data from system B and 8 digits means a link to system C of another division.

Post Salesforce implementation, such fields are stored/referred in Salesforce (via data migration) as externalId fields for identifying data from external systems. However the need to run a few integrations or batch jobs or just perform some legacy system match doesn't perish and sometimes we need to filter rows in SOQL depending on the length of characters in that externalId field.

Since there isn't any inbuilt Length() function, we can make use of the following SOQL
Select externalId from sObject where externalId like '____'
Note: Those are underscores and the number of underscores is the number of characters or the Length that you need.

Select externalId from sObject where (not externalId like '_____________')

No comments :

Powered by Blogger.