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

Question

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

Select              
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?

Answer 1

This caters for spaces as well.

(len(rtrim(ltrim(isnull(MyField,'')))) !=0

Answer 2

SELECT
   CASE WHEN LEN(listing.OfferText) > 0 THEN listing.OfferText 
        ELSE COALESCE(Company.OfferText, '') END 
   AS Offer_Text,

... 

In this example, if listing.OfferText is NULL, the LEN() function should also return NULL, but that's still not > 0.

Update

I've learned some things in the 5 1/2 years since posting this, and do it much differently now:

COALESCE(NULLIF(listing.OfferText,''), Company.OfferText, '')

This is similar to the accepted answer, but it also has a fallback in case Company.OfferText is also null. None of the other current answers using NULLIF() also do this.

Answer 3

Select              
CASE
    WHEN listing.OfferText is null or listing.OfferText = '' THEN company.OfferText
    ELSE COALESCE(Company.OfferText, '')
END As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id

Answer 4

Here is another solution:

SELECT Isnull(Nullif(listing.offertext, ''), company.offertext) AS offer_text, 
FROM   tbl_directorylisting listing 
       INNER JOIN tbl_companymaster company 
         ON listing.company_id = company.company_id

Answer 5

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

Answer 6

This simple combination of COALESCE and NULLIF should do the trick:

SELECT             
  Coalesce(NULLIF(listing.OfferText, ''), company.OfferText) As Offer_Text
...

Note: Add another empty string as the last COALESCE argument if you want the statement to return an empty string instead of NULL if both values are NULL.