How do I check if a Sql server string is null or e

2020-01-25 12:35发布

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?

17条回答
家丑人穷心不美
2楼-- · 2020-01-25 12:44

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
查看更多
迷人小祖宗
3楼-- · 2020-01-25 12:48

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
查看更多
看我几分像从前
4楼-- · 2020-01-25 12:48
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
查看更多
时光不老,我们不散
5楼-- · 2020-01-25 12:48

this syntax :

SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)

worked for me in Microsoft SQL Server 2008 (SP3)

查看更多
一纸荒年 Trace。
6楼-- · 2020-01-25 12:49
[Column_name] IS NULL OR LEN(RTRIM(LTRIM([Column_name]))) = 0
查看更多
ゆ 、 Hurt°
7楼-- · 2020-01-25 12:52

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
查看更多
登录 后发表回答