CODEWITHSUNDEEP
mysqlsqlcoalesce
James Glass
James Glass

Reputation: 4300

COALESCE() for blank (but not null) fields

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?

Upvotes: 22

Views: 31278

Answers (4)

Thangavel
Thangavel

Reputation: 11

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.

Upvotes: 1

Beachhouse
Beachhouse

Reputation: 5052

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)

Upvotes: 13

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You can use a CASE expression:

CASE WHEN Field1 <> '' THEN Field1 ELSE Field2 END

Upvotes: 6

Andrea Ligios
Andrea Ligios

Reputation: 50203

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.

Upvotes: 35

Related Questions