在select语句中使用sql -不是null

I can't seem to figure out how to use the opposite of isnull or ifnull statements in sql. I need to say if a.Error1 is not null -- then print the ' - ' and the + CHAR(13)+CHAR(10). Basically There should be no dash or no new line break if the a.Error1 comes back null. So print the information if the field isn't null.

我似乎不知道如何在sql中使用isnull或ifnull语句的反义词。我得说如果。Error1不是null——然后打印' - '和+CHAR(13)+CHAR(10)。基本上a不应该有破折号或新的换行符。Error1返回null。因此，如果字段不是null，就打印信息。

select a. .... 
' - ' + a.Error1 + CHAR(13)+CHAR(10) + 
' - ' + a.Error2 + CHAR(13)+CHAR(10) + 
' - ' + a.Error3 + CHAR(13)+CHAR(10) + 
' - ' + a.Error4 + CHAR(13)+CHAR(10) + 
' - ' + a.Error5 + CHAR(13)+CHAR(10) + 
' - ' + a.Error6                      as 'error_message'
... 
from table1 a

For example if for a given record error1, 2 and 5 returned output I would like the output to be as follows:
- Error1: There was a ...
- Error2: ....
- Error5: The data was ...

例如，如果给定记录error1、2和5返回输出，我希望输出如下:- error1:有……- Error2:....- Error5:数据是……

If no errors existed for that row it should simply be an empty/null field.

如果该行不存在错误，那么它应该是一个空/空字段。

3 个解决方案

#1

You can use CASE:

你可以用例:

SELECT a. ....
       (CASE WHEN a.Error1 IS NOT NULL 
             THEN ' - ' + a.Error1 + CHAR(13)+CHAR(10)  
             ELSE ''
        END) +
       (CASE WHEN a.Error2 IS NOT NULL 
             THEN ' - ' + a.Error2 + CHAR(13)+CHAR(10)  
             ELSE ''
        END) +
       (CASE WHEN a.Error3 IS NOT NULL 
             THEN ' - ' + a.Error3 + CHAR(13)+CHAR(10)  
             ELSE ''
        END) +

   ...etc

#2

The COALESCE function does what you want here. The result of COALESCE is the first NOT NULL value it is passed. Below we use '', which is distinct from NULL so that the outer + is always applied to NOT NULL strings.

联合函数在这里做你想做的。合并的结果是传递的第一个非空值。下面我们使用"，它与NULL是不同的，因此外部+总是被应用到非空字符串。

e.g.

如。

select a. .... 
    COALESCE( ' - ' + a.Error1 + CHAR(13)+CHAR(10), '' ) + 
    COALESCE( ' - ' + a.Error2 + CHAR(13)+CHAR(10), '' ) + 
    COALESCE( ' - ' + a.Error3 + CHAR(13)+CHAR(10), '' ) + 
    COALESCE( ' - ' + a.Error4 + CHAR(13)+CHAR(10), '' ) + 
    COALESCE( ' - ' + a.Error5 + CHAR(13)+CHAR(10), '' ) + 
    COALESCE( ' - ' + a.Error6 , '' ) as 'error_message'
... 
from table1 a

#3

SELECT (CASE WHEN a.Error1 IS NOT NULL
            THEN ' - ' + a.Error1 + CHAR(13)+CHAR(10) +
            ELSE a.Error1
            END) +
      (CASE WHEN a.Error2 IS NOT NULL
            THEN ' - ' + a.Error2 + CHAR(13)+CHAR(10) +
            ELSE a.Error2
            END) +
    .....etc

#1