function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MetaWhatMetaWhat 

Filter Empty Strings (Not Null) in SOQL.

Interesting behavior with SOQL query.

Here is the setup.

Some_Field__c is a Text length 20.

Some_Field__c is not null

This is proven by running

SELECT Some_Field__c FROM Account  WHERE Id ='0013000000Ik1srAAB'  and Some_Field__c !=null

and it returns the row.

And

SELECT Some_Field__c FROM Account  WHERE Id ='0013000000Ik1srAAB'  and Some_Field__c = null

does not return a row.

The row is an empty string but

SELECT Some_Field__c FROM Account  WHERE Id ='0013000000Ik1srAAB'  and Some_Field__c = ''

doesnt return anything.

Also,

SELECT Some_Field__c FROM Account  WHERE Id ='0013000000Ik1srAAB'  and Some_Field__c = '%'

doesn't return anything.


This is happening on a summer 08 sandbox org.

How do I filter on the empty string?

devjdk470devjdk470
I haven't tested it, but you should be able to use ',' to filter out nulls, similar to how it works in Salesforce filters:

http://ideas.salesforce.com/article/show/10093853
jennekingjenneking

Try this...

 

FROM Account WHERE FieldName != null 

 

This works:  != null  

 

When i used the Add Condition in data loader, I tried Operation of "not equals" and a value of Null and it retuned:

 

     FROM Account WHERE AccountNumber != 'Null' 

 

The 'Null' is looking for a value of the work Null... so no good.

 

When i use the Add Condition in data loader, and try Operation of "not equals" and i leave the Value empty, it returns:

 

    FROM Account WHERE AccountNumber != '' 

 

The != '' works as well.

 

 
Ajay K DubediAjay K Dubedi
Hi Meta,

1-If you have value of this field Some_Field__c and comparing to null value with ID then return nothing.
  Becoz query does not have this type of data, therefore, doesnt return anything.
2-If you have empty value of this field and comparing to null empty string or null value with Id the query return nothing.
  query does not have this type of data therefore doesn't return anything.
  
3-If you want to return Id, and some other field value whenever Some_Field__c field value is empty or null.
  then it returns the row. 
 
String s ='';
String query = ('Select Id,Some_Field__c from account where Some_Field__c=\'' + s+'\'');
List<sObject> obj = database.query(query);
System.debug('Your object field list--->'+obj);

You can also use this.

String query = ('Select Id, Some_Field__c from account where  Some_Field__c = Null');
List<sObject> obj = database.query(query);
System.debug('Your object field list--->'+obj);

OR
string query='Select Id, Some_Field__c from account where Some_Field__c = ' '\';
List<sObject> obj = database.query(query);
System.debug('Your object field list--->'+obj);


Please let me know if you have any query.
Please mark it as best Answer if you find it helpful.

Thank You
Ajay Dubedi
Shubham NandwanaShubham Nandwana
Hi MetaWhat,
You can directly check for null values as mentioned in https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_null.htm 
SELECT Id
FROM Case
WHERE Contact.LastName = null
  
SELECT Id
FROM Case
WHERE Contact.LastName = ''

Hope it helps.

Shubham Nandwana.
AppPerfect Corp.
salesforce@appperfect.com
408-252-4100
http://www.appperfect.com/services/salesforce/
Salesforce Development & Operations Experts
Muralikrishna PolimaraMuralikrishna Polimara
=null //NULL Check

!=null //NOT NULL Check
See Salesforce Docs here (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_null.htm)