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.
我有两个字段要与MySQL的函数COALESCE()进行比较。例如,合并(Field1,Field2)。问题是，Field1有时是空的，但不是空的;因为它不是null COALESCE()选择Field1，即使它是空的。在这种情况下，我需要它来选择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?
我知道我可以在查询中编写if-then-else (CASE)语句来检查它，但是是否存在一个很好的简单函数，比如用于空格-but-not-null字段的COALESCE() ?
3 个解决方案
#1
25
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.
如果Field1为空，则NULLIF返回NULL;如果字段为空，则IFNULL返回Field1;如果字段为空，则返回Field1;反之，则返回Field2。
#2
6
You can use a CASE expression:
您可以使用案例表达式:
CASE WHEN Field1 <> '' THEN Field1 ELSE Field2 END
#3
6
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 ''.
我知道我在这里的聚会上迟到了，但是仍然可以使用COALESCE()来实现这一点。如果您的值为NULL或"。
Select COALESCE(NULLIF(Field1,''), Field2)
#1
25
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.
如果Field1为空，则NULLIF返回NULL;如果字段为空，则IFNULL返回Field1;如果字段为空，则返回Field1;反之，则返回Field2。
#2
6
You can use a CASE expression:
您可以使用案例表达式:
CASE WHEN Field1 <> '' THEN Field1 ELSE Field2 END
#3
6
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 ''.
我知道我在这里的聚会上迟到了，但是仍然可以使用COALESCE()来实现这一点。如果您的值为NULL或"。
Select COALESCE(NULLIF(Field1,''), Field2)