如果列有空值或空值[重复]，则赋值-1

This question already has an answer here:

这个问题已经有了答案:

Replacing NULL and empty string within Select statement 3 answers
在Select语句3中替换空字符串

I have a case where if my column has NULL value or blank value I have to return '-1'. I have NULL check as follows

我有这样一种情况，如果我的列有空值或空值，我必须返回'-1'我有下面的零校验

ISNULL(IEF.Col1,'-1').

How can i check for blank value and null value(as above) at the same time. Means, if the column has blank value still in select -1 should be returned and if NULL also -1 should be returned.

如何同时检查空值和空值(如上所示)?表示，如果列的空值仍然在select -1中，则应该返回，如果NULL也应该返回-1。

5 个解决方案

#1

You can treat empty as null with NULLIF:

可以用NULLIF将empty处理为null:

ISNULL(NULLIF(IEF.LeadingValue, ''), '-1')

#2

You can use NULLIF like:

可以使用NULLIF:

select ISNULL(NULLIF(IEF.Col1, ''), -1) AS Col1 from table

From Microsoft Website:

从微软的网站:

Returns a null value if the two specified expressions are equal. For example, SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; returns NULL for the first column (4 and 4) because the two input values are the same. The second column returns the first value (5) because the two input values are different.

如果指定的两个表达式相等，则返回空值。例如，选择NULLIF(4,4)作为相同，NULLIF(5,7)作为不同;返回第一个列(4和4)的空值，因为两个输入值是相同的。第二列返回第一个值(5)，因为两个输入值不同。

#3

use case inside isnull -

isnull -中的用例

ISNULL( case when IEF.LeadingValue ='' then null else IEF.LeadingValue end ,'-1')

#4

The following code should work:

下列守则应有效:

ISNULL(NULLIF(RTRIM(LTRIM(IEF.LeadingValue)),''),'-1')

Explanation:

解释:

NULLIF - compares two strings , if identical then return null
NULLIF -比较两个字符串，如果相同，则返回null
RTRIM - right trim , remove right spaces in string
修剪-正确修剪，删除正确的空间在字符串
LTRIM - left trim , remove left spaces in string
LTRIM -左修剪，在字符串中删除左空间。

#5

A little trick is: - this will check value just one time -

一个小技巧是:-这将只检查一次值-

select
    case when IEF.Col1 <> '' then IEF.Col1
    else '-1'
    end
from
    IEF;

[ SQL Fiddle Demo]

(SQL小提琴演示)

Note: Comparing a null value by using <> operator always returns false.

注意:使用<>操作符比较空值总是返回false。

#1