As a Salesforce developer, you likely rely on the Salesforce Object Query Language (SOQL) for almost every automation and data retrieval task. However, you may eventually run into a frustrating limitation: SOQL does not have a native LEN() or LENGTH() function to filter records based on the number of characters in a field.

Whether you are trying to find Leads with unusually long city names, identify malformed postal codes, or perform data cleanup for a migration, knowing how to query by string length is essential. In this guide, we will explore the two most effective workarounds for this limitation: using custom formula fields and leveraging the LIKE wildcard operator.

Why SOQL Doesn't Support String Length Naturally

Unlike standard SQL dialects (like MySQL or PostgreSQL), SOQL is designed to be highly optimized for multi-tenant environments. Because calculating string lengths on the fly can be resource-intensive for a database engine, Salesforce omits this function from the query language to ensure performance stability. To achieve this filtering, you must either prepare the data beforehand or use pattern matching.

The most robust and performant way to filter by length is to create a custom formula field on the object. This offloads the calculation from the query engine to the record itself, allowing Salesforce to index the value if necessary.

Step 1: Create the Formula Field

  1. Navigate to Setup > Object Manager and select your object (e.g., Lead).
  2. Create a new field of type Formula.
  3. Set the return type to Number (with 0 decimal places).
  4. Use the LEN() function in the formula editor:
LEN(City__c)

Give the field a descriptive name, such as City_Length__c.

Step 2: Query the Formula Field

Once the field is created, you can query it just like any other field in SOQL. To find all leads where the city length is greater than 20 characters, use the following syntax:

SELECT Id, Name, City__c 
FROM Lead 
WHERE City_Length__c > 20

Pros: - Extremely easy to read and maintain. - Supports all comparison operators (>, <, =, !=). - Can be indexed (as a deterministic formula) for better performance on large datasets.

Cons: - Uses one of your custom field slots on the object.

Method 2: The LIKE Wildcard Hack

If you are in an environment where you cannot add new fields, or if you need a quick one-time query in the Query Editor, you can use the underscore (_) wildcard. In SOQL, the underscore represents exactly one character, while the percent sign (%) represents zero or more characters.

Querying for an Exact Length

To find records where a field is exactly 5 characters long (such as a Zip Code), you can use five underscores:

SELECT Id FROM User WHERE Username LIKE '_____'

Querying for "Greater Than" Length

To find records where a field is longer than a specific threshold, combine underscores with the percent wildcard. For example, to find records where the City__c is longer than 20 characters, you would use 21 underscores followed by a %:

SELECT Id FROM Lead WHERE City__c LIKE '_____________________%'

Querying for "Not Equal" to Length

You can also use the NOT operator. To find all users where the username is NOT exactly 3 characters:

SELECT Id FROM User WHERE (NOT Username LIKE '___')

Pros: - No schema changes required. - Works instantly in the Developer Console or Workbench.

Cons: - Very difficult to read and error-prone (counting 20+ underscores is tedious). - Generally slower performance than filtering on a numeric field. - Does not work well for "less than" logic without complex nesting.

Method 3: Post-Processing with Apex

If your dataset is small enough to fit within heap limits, you can query the records and filter them using Apex. This is often useful for one-time data cleanup scripts.

List<Lead> longCityLeads = new List<Lead>();
for (Lead l : [SELECT Id, City__c FROM Lead WHERE City__c != null]) {
    if (l.City__c.length() > 20) {
        longCityLeads.add(l);
    }
}
System.debug('Found ' + longCityLeads.size() + ' records.');

While this works, it is the least efficient method because it requires the application server to process every record, rather than letting the database filter the results.

Frequently Asked Questions

Does the LEN() formula count spaces?

Yes, the LEN() function in Salesforce formulas and the .length() method in Apex both count spaces and special characters as part of the string length.

Is there a performance limit to using the LIKE wildcard with many underscores?

Yes. Using a large number of wildcards can lead to full table scans. If you are querying millions of records, the Formula Field approach is significantly faster and more reliable.

Can I use this for standard fields like Name or AccountNumber?

Absolutely. The same logic applies. For standard fields, you would still create a custom formula field that references the standard field (e.g., LEN(Name)).

Wrapping Up

While SOQL doesn't provide a native LEN() function, the platform provides flexible alternatives to get the job done. For long-term solutions and better performance, Method 1 (Formula Fields) is the industry standard. For quick debugging or environments where you lack admin permissions, Method 2 (LIKE Wildcards) is a clever trick to keep in your toolkit.

By implementing these strategies, you can maintain higher data quality and ensure your Salesforce instance remains clean and organized.