sqlsql-servernullisnullifnull

using sql - Is not null in a select statement


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.

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 ...

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


Solution

  • 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