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?
This question is related to
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
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)
[Column_name] > ' ' excludes Nulls and empty strings. There is a space between the single quotes.
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
this syntax :
SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)
worked for me in Microsoft SQL Server 2008 (SP3)
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
To prevent the records with Empty or Null value in SQL result
we can simply add ..... WHERE Column_name != '' or 'null'
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
this syntax :
SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)
worked for me in Microsoft SQL Server 2008 (SP3)
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
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
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.
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...
[Column_name] IS NULL OR LEN(RTRIM(LTRIM([Column_name]))) = 0
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.
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
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.
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.
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
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
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
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
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
To prevent the records with Empty or Null value in SQL result
we can simply add ..... WHERE Column_name != '' or 'null'
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
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
This caters for spaces as well.
(len(rtrim(ltrim(isnull(MyField,'')))) !=0
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
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)
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
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.
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...
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
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
This caters for spaces as well.
(len(rtrim(ltrim(isnull(MyField,'')))) !=0
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
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.
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
[Column_name] > ' ' excludes Nulls and empty strings. There is a space between the single quotes.
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
[Column_name] IS NULL OR LEN(RTRIM(LTRIM([Column_name]))) = 0
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.
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
Source: Stackoverflow.com