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

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.

Answer 2

I know this is an old thread but I just saw one of the earlier posts above and it is not correct.

If you are using LEN(...) to determine whether the field is NULL or EMPTY then you need to use it as follows:

...WHEN LEN(ISNULL(MyField, '')) < 1 THEN NewValue...

Answer 3

[Column_name] > ' ' excludes Nulls and empty strings. There is a space between the single quotes.

Answer 4

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;

Answer 5

To prevent the records with Empty or Null value in SQL result

we can simply add ..... WHERE Column_name != '' or 'null'