How do I check if a Sql server string is null or empty
Sql Server-2005Sql Server-2005 Problem Overview
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?
Sql Server-2005 Solutions
Solution 1 - Sql Server-2005
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;
Solution 2 - Sql Server-2005
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.
Solution 3 - Sql Server-2005
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
Solution 4 - Sql Server-2005
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
Solution 5 - Sql Server-2005
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
Solution 6 - Sql Server-2005
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.
Solution 7 - Sql Server-2005
Use the LEN function to check for null or empty values. You can just use LEN(@SomeVarcharParm) > 0. This will return false if the value is NULL, '', or ' '. This is because LEN(NULL) returns NULL and NULL > 0 returns false. Also, LEN(' ') returns 0. See for yourself run:
SELECT
CASE WHEN NULL > 0 THEN 'NULL > 0 = true' ELSE 'NULL > 0 = false' END,
CASE WHEN LEN(NULL) > 0 THEN 'LEN(NULL) = true' ELSE 'LEN(NULL) = false' END,
CASE WHEN LEN('') > 0 THEN 'LEN('''') > 0 = true' ELSE 'LEN('''') > 0 = false' END,
CASE WHEN LEN(' ') > 0 THEN 'LEN('' '') > 0 = true' ELSE 'LEN('' '') > 0 = false' END,
CASE WHEN LEN(' test ') > 0 THEN 'LEN('' test '') > 0 = true' ELSE 'LEN('' test '') > 0 = false' END
Solution 8 - Sql Server-2005
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...
Solution 9 - Sql Server-2005
this syntax :
SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)
worked for me in Microsoft SQL Server 2008 (SP3)
Solution 10 - Sql Server-2005
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
Solution 11 - Sql Server-2005
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.
Solution 12 - Sql Server-2005
Here's a solution, but I don't know if it's the best....
Select
Coalesce(Case When Len(listing.Offer_Text) = 0 Then Null Else listing.Offer_Text End, company.Offer_Text, '') As Offer_Text,
from tbl_directorylisting listing
Inner Join tbl_companymaster company
On listing.company_id= company.company_id
Solution 13 - Sql Server-2005
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
Solution 14 - Sql Server-2005
To check if variable is null or empty use this:
IF LEN(ISNULL(@var, '')) = 0
-- Is empty or NULL
ELSE
-- Is not empty and is not NULL
Solution 15 - Sql Server-2005
To prevent the records with Empty
or Null
value in SQL result
we can simply add ..... WHERE Column_name != '' or 'null'
Solution 16 - Sql Server-2005
[Column_name] IS NULL OR LEN(RTRIM(LTRIM([Column_name]))) = 0
Solution 17 - Sql Server-2005
[Column_name] > ' ' excludes Nulls and empty strings. There is a space between the single quotes.
Solution 18 - Sql Server-2005
When dealing with VARCHAR
/NVARCHAR
data most other examples treat white-space the same as empty string which is equal to C# function IsNullOrWhiteSpace
.
This version respects white-space and works the same as the C# function IsNullOrEmpty
:
IIF(ISNULL(DATALENGTH(val), 0) = 0, whenTrueValue, whenFalseValue)
Simple test:
SELECT
'"' + val + '"' AS [StrValue],
IIF(ISNULL(DATALENGTH(val), 0) = 0, 'TRUE', 'FALSE') AS IsNullOrEmpty
FROM ( VALUES
(NULL),
(''),
(' '),
('a'),
('a ')
) S (val)
Solution 19 - Sql Server-2005
This caters for spaces as well.
(len(rtrim(ltrim(isnull(MyField,'')))) !=0