mysql - COALESCE() for blank (but not null) fields

ID : 274532

viewed : 32

Tags : mysqlcoalescemysql





Top 5 Answer for mysql - COALESCE() for blank (but not null) fields

vote vote

97

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.

vote vote

87

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) 
vote vote

70

You can use a CASE expression:

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

67

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.

vote vote

58

Top 3 video Explaining mysql - COALESCE() for blank (but not null) fields







Related QUESTION?