22

I have two fields that I'm comparing with MySQL's function COALESCE(). For example, COALESCE(Field1, Field2). The problem is, Field1 is sometimes blank but not null; since it's not null COALESCE() selects Field1, even though its blank. In that case, I need it to select Field2.

I know I can write a if-then-else (CASE) statement in the query to check for this, but is there a nice simple function like COALESCE() for blank-but-not-null fields?

Andrea Ligios
  • 49,480
  • 26
  • 114
  • 243
Luke Shaheen
  • 4,262
  • 12
  • 52
  • 82

4 Answers4

33
SELECT IFNULL(NULLIF(Field1,''),Field2)

NULLIF returns a NULL if Field1 is blank, while IFNULL returns Field1 if it's not blank or NULL and Field2 otherwise.

Andrea Ligios
  • 49,480
  • 26
  • 114
  • 243
  • 5
    I could do the same, but with COALESCE correct? `COALESCE(NULLIF(Field1,''),Field2)` - is there a speed/processing load difference? – Luke Shaheen Nov 05 '12 at 16:16
  • 1
    Interesting response about `COALESCE` vs `IFNULL` here: http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster – Luke Shaheen Nov 05 '12 at 16:17
12

I know I'm late to the party here, but there is a way to do this while still using COALESCE(). This would then work if your value was NULL or ''.

Select COALESCE(NULLIF(Field1,''), Field2)
Beachhouse
  • 4,972
  • 3
  • 25
  • 39
6

You can use a CASE expression:

CASE WHEN Field1 <> '' THEN Field1 ELSE Field2 END
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
0

Use CASE, for comparing both NULL and Blank.

SELECT CASE 
        WHEN Field1 IS NULL
            OR LEN(LTRIM(RTRIM(Field1))) < 1
            THEN Field2
        ELSE Field1
        END;

For comparing blank, do not use '', use LEN, LTRIM, RTRIM. Sometimes, blank may be with more than one spaces.

Thangavel
  • 1
  • 2