using sql - Is not null in a select statement

Question

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.

possible duplicate of [How to concatenate columns properly using T-SQL?](http://stackoverflow.com/questions/5235391/how-to-concatenate-columns-properly-using-t-sql) — Panagiotis Kanavos, Jan 26 '15 at 17:00

Giorgos Betsos · Accepted Answer · 2015-01-26T16:48:47.237

6

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

edited Jan 26 '15 at 16:48

answered Jan 26 '15 at 15:53

Giorgos Betsos

71,379
9
63
98

Is there no way to use something like evaluate if the value is null? and print output based on that? Also there is possibility that there will be multiple errors for a given row, so we need the output to include error1 and error2 etc. We can't use case because then once one error is found the other cases won't be evaluated. – Elizabeth Jan 26 '15 at 15:54
@Elizabeth `CASE` is the way to go in `SQL Server`. So, if `a.Error1` is null what kind of output do you want? – Giorgos Betsos Jan 26 '15 at 15:57
If a.Error1 is null, simply a white space or empty field. Otherwise the actually value should print (for all the errors) – Elizabeth Jan 26 '15 at 15:59
@Elizabeth That's exactly what the above code does: If `a.Error1` is null then an empty string `('')` is used in it's place. You can substitute `('')` with `(' ')` to get some white space. – Giorgos Betsos Jan 26 '15 at 16:01
Right, the above code is great - but doesn't the case statement work that if case1 is correct case2 isn't evaluated etc? I want all cases evaluated and all errors printed because its possible that there are multiple errors per line. – Elizabeth Jan 26 '15 at 16:03
@Elizabeth Each `CASE` is evaluated *independently* from each other. In case no errors exist, then you wil get `''` as output. Else if, for example, only error1, 2 and 5 returned output then the `CASE` statements will produce a string based on these errors exclusively. – Giorgos Betsos Jan 26 '15 at 16:08
Thank you I didn't see the + so i missed the concatenation here. That said when the code runs it seems put a `' - '` if the value is null, i'm not sure why that is happening. – Elizabeth Jan 26 '15 at 16:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/69613/discussion-between-giorgos-betsos-and-elizabeth). – Giorgos Betsos Jan 26 '15 at 16:33

score 3 · Answer 2 · answered May 22 '20 at 09:52

Yes! i know i'm like 5 years too late but i too enountered this problem.

It's weird how it doesn't exist some kind of !ISNULL() but whatever.

Try this for a cleaner code:

select a. .... 
 IIF(a.Error1 IS NOT NULL, ' - ' + a.Error1 + CHAR(13)+CHAR(10) , '') as Error1,
 IIF(a.Error1 IS NOT NULL, ' - ' + a.Error1 + CHAR(13)+CHAR(10) , '') as Error2
from table1 a

Learn more about IIF() function : SQL Server IIF Function

Caleth · Answer 3 · 2015-01-26T17:09:23.980

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.

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

edited Jan 26 '15 at 17:09

answered Jan 26 '15 at 16:57

Caleth

52,200
2
44
75

You should explain why this works and is better than other answers, ie that concatenating with NULL returns NULL – Panagiotis Kanavos Jan 26 '15 at 17:00

score 0 · Answer 4 · answered Jan 26 '15 at 15:54

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

using sql - Is not null in a select statement

4 Answers4