Loading

Data 360: Handling Empty Values in CONCAT Function in Calculated Insights

Udgivelsesdato: Dec 2, 2025
Beskrivelse

Behavior of CONCAT with Empty Values

The CONCAT function is commonly used to merge multiple fields into a single string. However, when any of the concatenated fields contain a NULL value, the result of the concatenation is NULL. This behavior is particularly important when working with data that may have missing or empty values.

Example Query:

Consider the following SQL query:

SELECT 
    CONCAT(ssot__Individual__dlm.ssot__FirstName__c, ' - ', ssot__Individual__dlm.KQ_Id__c) AS FullName_KQId__c, 
    COUNT(ssot__Individual__dlm.ssot__Id__c) AS RecordCount__c 
FROM ssot__Individual__dlm 
GROUP BY CONCAT(ssot__Individual__dlm.ssot__FirstName__c, ' - ', ssot__Individual__dlm.KQ_Id__c);


In this query:


  • 

    FullName_KQId__c is created by concatenating ssot__FirstName__c and KQ_Id__c with a hyphen (-) as a separator.
    

  • 

    If either ssot__FirstName__c or KQ_Id__c is NULL, the entire FullName_KQId__c field will be NULL.
    



Expected Output


ssot__FirstName__cKQ_Id__cFullName_KQId__c
John123John - 123
JaneNULLNULL
NULL456NULL


 
Løsning
Workaround for Handling Empty Values


To avoid NULL values impacting the concatenated result, consider using a CASE statement to replace NULL values with an empty string. For example:




SELECT 
    CONCAT( 
        CASE WHEN ssot__Individual__dlm.ssot__FirstName__c IS NULL THEN '' ELSE ssot__Individual__dlm.ssot__FirstName__c END, 
        ' - ', 
        CASE WHEN ssot__Individual__dlm.KQ_Id__c IS NULL THEN '' ELSE ssot__Individual__dlm.KQ_Id__c END 
    ) AS FullName_KQId__c, 
    COUNT(ssot__Individual__dlm.ssot__Id__c) AS RecordCount__c 
FROM ssot__Individual__dlm 
GROUP BY 
    CONCAT( 
        CASE WHEN ssot__Individual__dlm.ssot__FirstName__c IS NULL THEN '' ELSE ssot__Individual__dlm.ssot__FirstName__c END, 
' - ', 
        CASE WHEN ssot__Individual__dlm.KQ_Id__c IS NULL THEN '' ELSE ssot__Individual__dlm.KQ_Id__c END 
    );




Key Takeaways


  • 

    If any field in the CONCAT function is NULL, the entire result is NULL.
    

  • 

    Use a CASE statement to replace NULL values with an empty string and ensure concatenation works as expected.
    

  • 

    Proper handling of empty values can prevent unexpected data loss and improve the accuracy of reports.
    



By implementing these best practices, you can ensure your Calculated Insights remain reliable and meaningful even when dealing with incomplete data.
Vidensartikelnummer
004269012
 
Indlæser
Salesforce Help | Article