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

Question

I think this:

SELECT 
  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;
else
  temp := listing.Offer_Text; // may now be null or non-null, but not ''
// b) ISNULL:
if (temp is null)
  result := true;
else
  result := false;

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.

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

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

You can use ISNULL and check the answer against the known output:

SELECT case when ISNULL(col1, '') = '' then '' else col1 END AS COL1 FROM TEST

In SQL Server 2012 you have IIF, e.g you can use it like

SELECT IIF(field IS NULL, 1, 0) AS IsNull

The same way you can check if field is empty.