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...
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.OfferTex
t if listing.Offertext
is an empty string, as well as if it's null.
What's the best performing solution?
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