0

How to check if a text field is not null, not empty, doesn't contain white space in SOQL? Other than using something like,

AND (field__c != null AND field__c != '' AND Field__c != ' ')

in the WHERE clause of my SOQL, which isn't taking all the situations into account, how can I do this? I'm looking at the string class method such as,

String.isNotBlank()

I know I can iterate over the result of the query and filter out what I don't need but I'm asking this question to avoid that if possible to implement something within the query itself. Thanks!

Improve this question
0

1 Answer 1

Reset to default
1

SOQL cannot do this directly, it has no ISEMPTY() function. Even if it did, using negative filters has implications with query selectivity.

The best approach here, if you want to avoid doing the work in Apex, would be to create a formula field (boolean/checkbox) that tells you whether or not a field is blank, and then including that in your query (either WHERE Formula_Field__c = true or WHERE Formula_Field__c = false)

Improve this answer
9
  • Thanks for confirming that it can't be done through SOQL. I really like your suggestion as well. Which is the best way to do this though in terms of performance and best practices?
    – Bahman.A
    Feb 22, 2021 at 20:30
  • 1
    @Bahman.A I doubt you'd be able to tell a difference in terms of performance (also, microoptimization is bad). As for best practice, I don't think that's really established here. There are arguments that could be made for both approaches (apex / formula field).
    – Derek F
    Feb 22, 2021 at 20:38
  • 1
    One thing I forgot to mention, Salesforce doesn't store empty strings (i.e. '') in sobject records, it just gets treated as null.
    – Derek F
    Feb 22, 2021 at 20:39
  • 1
    The isBlank() will catch null, '' (empty string, only an issue for records created/altered in memory), and ' ' (string with one blank space). My point was that '' and null are basically equivalent when you're writing a query.
    – Derek F
    Feb 22, 2021 at 21:01
  • 1
    @Bloke Nothing official that I'm aware of. It's just a general observation that I and several others have made. It might be a result of some quirk in Oracle's RDBMS (which Salesforce uses).
    – Derek F
    Jan 4 at 18:08

You must log in to answer this question.

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