20

I am looking for a way to query all leads which have the length of city__c > 20 characters in SOQL.

I cant seem to find anything like a length() in SOQL. Any workarounds? Or am i missing something obvious on the documentation

2 Answers 2

17

You need to create a new formula filed of type NUMBER and insert a simple formula to it that calculates a length of the string:

LEN(Street__c)

LEN Formula

Then you can query on this field:

Select ... From ...  Where FormulaField___c > 20
31
SELECT Id FROM User WHERE (NOT Username LIKE '___')

The above SOQL query will return all user rows where the username is not 3 characters.

SELECT Id FROM User WHERE Username LIKE '______'

The above SOQL query will return all user rows where the username is 5 characters.

4
  • I was surprised to see the positive scenario work, but the negative results in a malformed query (not like). Are you sure that works ? Oct 14, 2014 at 16:10
  • 2
    Thank you for this information, Mark. FYI I had to put parentheses around the negative to make it work: (NOT Username Like '___') Nov 13, 2014 at 15:56
  • 4
    Nice find! This definitely works. Nov 26, 2014 at 10:40
  • 5
    If you are looking for greater than you can use LIKE '_____%' (<-- greater than 5). Aug 10, 2022 at 15:22

You must log in to answer this question.

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