我想检查数据，但如果它为空或空忽略它。目前查询如下…
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
但我想找个伴。OfferText如果列表。Offertext是一个空字符串，如果它是null。
最好的解决方案是什么?
当前回答
您可以使用ISNULL并根据已知输出检查答案:
SELECT case when ISNULL(col1, '') = '' then '' else col1 END AS COL1 FROM TEST
其他回答
这里有一个解决方案，但我不知道它是否是最好的....
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
这个语法:
SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)
在Microsoft SQL Server 2008 (SP3)为我工作
在SQL Server 2012中你有IIF，例如你可以像这样使用它
SELECT IIF(field IS NULL, 1, 0) AS IsNull
同样的方法可以检查字段是否为空。
这里有另一个解决方案:
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
