如何检查Sql服务器字符串是空还是空

I want to check for data but ignore it if it's null or empty. Currently the query is as follows...

我想检查数据但是如果它为null或为空则忽略它。目前查询如下......

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.

但是我想得到company.OfferText,如果listing.Offertext是一个空字符串,以及它是否为null。

What's the best performing solution?

什么是性能最佳的解决方案?

15 个解决方案

#1

368

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;

#2

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.

在此示例中,如果listing.OfferText为NULL,则LEN()函数也应返回NULL,但仍不是> 0。

Update

I've learned some things in the 5 1/2 years since posting this, and do it much differently now:

自从发布这篇文章以来的5年半里,我已经学到了一些东西,现在做的却大不相同:

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.

这类似于已接受的答案,但如果Company.OfferText也为null,它也会有后备。使用NULLIF()的其他当前答案也都不会这样做。

#3

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

#4

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

#5

You can use ISNULL and check the answer against the known output:

您可以使用ISNULL并根据已知输出检查答案:

SELECT case when ISNULL(col1, '') = '' then '' else col1 END AS COL1 FROM TEST

#6

In SQL Server 2012 you have IIF, e.g you can use it like

在SQL Server 2012中,您有IIF,例如,您可以使用它

SELECT IIF(field IS NULL, 1, 0) AS IsNull

The same way you can check if field is empty.

您可以通过相同的方式检查字段是否为空。

#7

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:

使用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

#8

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

#9

This simple combination of COALESCE and NULLIF should do the trick:

这个COALESCE和NULLIF的简单组合应该可以解决这个问题:

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.

注意:如果希望语句返回空字符串而不是NULL(如果两个值都为NULL),则添加另一个空字符串作为最后一个COALESCE参数。

#10

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

#11

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

#12

this syntax :

这个语法:

SELECT *
FROM tbl_directorylisting listing
WHERE (civilite_etudiant IS NULL)

worked for me in Microsoft SQL Server 2008 (SP3)

在Microsoft SQL Server 2008(SP3)中为我工作

#13

To prevent the records with Empty or Null value in SQL result

防止SQL结果中带有Empty或Null值的记录

we can simply add ..... WHERE Column_name != '' or 'null'

我们可以简单地添加..... WHERE Column_name!=''或'null'

#14

[Column_name] IS NULL OR LEN(RTRIM(LTRIM([Column_name]))) = 0

#15

I know this is an old thread but I just saw one of the earlier post above and it is not correct. If you are using LEN() to determine whether the filed is NULL or EMPTY then you need to use it as follows:

我知道这是一个旧线程,但我刚看到上面的一个帖子,这是不正确的。如果您使用LEN()来确定字段是NULL还是EMPTY,那么您需要按如下方式使用它:

...WHEN LEN(ISNULL(MyField, '')) < 1 THEN NewValue...

#1

368