我想检查数据，但如果它为空或空忽略它。目前查询如下…
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。
最好的解决方案是什么?
当前回答
在处理VARCHAR/NVARCHAR数据时，大多数其他示例将空白视为空字符串，这等于c#函数IsNullOrWhiteSpace。
这个版本尊重空白，工作原理与c#函数IsNullOrEmpty相同:
IIF(ISNULL(DATALENGTH(val), 0) = 0, whenTrueValue, whenFalseValue)
简单的测试:
SELECT
'"' + val + '"' AS [StrValue],
IIF(ISNULL(DATALENGTH(val), 0) = 0, 'TRUE', 'FALSE') AS IsNullOrEmpty
FROM ( VALUES
(NULL),
(''),
(' '),
('a'),
('a ')
) S (val)
其他回答
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
您可以使用ISNULL并根据已知输出检查答案:
SELECT case when ISNULL(col1, '') = '' then '' else col1 END AS COL1 FROM TEST
使用LEN函数检查空值或空值。你可以使用LEN(@SomeVarcharParm) > 0。如果值为NULL， "，或''则返回false。这是因为LEN(NULL)返回NULL，而NULL > 0返回false。同样，LEN(' ')返回0。自己看，快跑:
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
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
这也迎合了空间。
(len(rtrim(ltrim(isnull(MyField,'')))) !=0
