How do I check if a Sql server string is null or empty

I want to check for data, but ignore it if it's null or empty. Currently the query is as follows...

Coalesce(listing.OfferText, company.OfferText, '') As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id      

But I want to get company.OfferText if listing.Offertext is an empty string, as well as if it's null.

What's the best performing solution?


  • I think this:

      ISNULL(NULLIF(listing.Offer_Text, ''), company.Offer_Text) AS Offer_Text
    FROM ...

    is the most elegant solution.

    And to break it down a bit in pseudo code:

    // a) NULLIF:
    if (listing.Offer_Text == '')
      temp := null;
      temp := listing.Offer_Text; // may now be null or non-null, but not ''
    // b) ISNULL:
    if (temp is null)
      result := true;
      result := false;