+ Start a Discussion
Admin User 10161Admin User 10161 

replace NULL value in SOQL query with empty string

Hi there,
In the following Apex code. I have created a string "query" in which I selected all the fields of a custom object.
for(String fieldName : fieldMap.keyset() )
        {
         if(strFields == null || strFields == '')
         {
           strFields = fieldName;
          }else{
           strFields = strFields + ' , ' + fieldName;
          }
         }

		query = 'SELECT ' + strFields + ' FROM ' + SobjectApiName + ' WHERE Id IN: buyIds';
        
        for(APTIVN__NBuyer__c buy: Database.query(query)) {
            SelectedBuyerIDs.add(buy);
        }
The problem is that for some fields for one particular row the value of this field is null. Is it possible to replace NULL value in an empty string on a SOQL query?
Best regards,
Best Answer chosen by Admin User 10161
Admin User 10161Admin User 10161
The solution was the following:
String strFields = '';
        for(String fieldName : fieldMap.keyset()) {
            if(fieldName != null) {
                if(strFields == null || strFields == '') {
                    strFields = fieldName;
                }
                else {
                    strFields = strFields + ' , ' + fieldName;
                }
            }
        }
        
        query = 'SELECT ' + strFields + ' FROM ' + SobjectApiName + ' WHERE Id IN: buyIds';
        
        for(APTIVN__NBuyer__c buy: Database.query(query)) {
            for(String field: fieldMap.keySet()) {
                if (buy.get(field) == null && field != 'lastvieweddate' && field != 'lastreferenceddate') {
                    buy.put(field, '');
                }
            }
            SelectedBuyerIDs.add(buy);
        }

 

All Answers

Abhishek BansalAbhishek Bansal
Hi ,

You can use the below code that will work in all the scenarios:
for(String fieldName : fieldMap.keyset()) {
	if(fieldName != null) {
		if(strFields == null || strFields == '') {
			strFields = fieldName;
		}
		else {
			strFields = strFields + ' , ' + fieldName;
		}
	}
}

query = 'SELECT ' + strFields + ' FROM ' + SobjectApiName + ' WHERE Id IN: buyIds';

for(APTIVN__NBuyer__c buy: Database.query(query)) {
	SelectedBuyerIDs.add(buy);
}

Let me know if you have any further questions.

Thanks,
Abhishek Bansal.​​​​​​
Admin User 10161Admin User 10161
Hi Abhishek,
Thank you for the answer.
The query still returns NULL value.
Let's consider for example the custom object 'Aptiv Buyer' with the following custom fields: id, name, email
idnameemail
123456John doeNULL
123467NULLJohn.smith@gmail.com
Then the query would return something like that
idnameemail
123456John doe‘’
123467‘’John.smith@gmail.com
Do you know how to perform that on SOQL query?
Abhishek BansalAbhishek Bansal
No this is not possible in SOQL. You need to add a code to convert the null values into Blank.
Why do you want to do this? Is there any specific requirement?
ali khan 63ali khan 63
Hello I have a web hosting website ehosting (https://ehosting.pk/web-hosting) and a car website hire a car (https://hireacar.pk) both are having issue of mysqli database.
Admin User 10161Admin User 10161
The solution was the following:
String strFields = '';
        for(String fieldName : fieldMap.keyset()) {
            if(fieldName != null) {
                if(strFields == null || strFields == '') {
                    strFields = fieldName;
                }
                else {
                    strFields = strFields + ' , ' + fieldName;
                }
            }
        }
        
        query = 'SELECT ' + strFields + ' FROM ' + SobjectApiName + ' WHERE Id IN: buyIds';
        
        for(APTIVN__NBuyer__c buy: Database.query(query)) {
            for(String field: fieldMap.keySet()) {
                if (buy.get(field) == null && field != 'lastvieweddate' && field != 'lastreferenceddate') {
                    buy.put(field, '');
                }
            }
            SelectedBuyerIDs.add(buy);
        }

 
This was selected as the best answer