如何在sql查询的空字段中显示文本?
问题描述:
我必须从数据库中提取客户列表,但如果第二个地址字段为空,我想显示文本None Provided。这是我的查询:如何在sql查询的空字段中显示文本?
select concat(first_name, " ", last_name) as CustomerName, address,
address2, postal_code
from customer
inner join address
using (address_id);
如果address2是空白,我想显示“none provided”。我怎么做?我相信答案非常简单,但我有一个大脑放屁,我无法在任何地方找到答案。
好吧,这是我结束了去:
case when address2=" " or address2 is null then 'None Provided'
else address2 end as address2,
有没有更好的方式来实现这一目标?
答
您可以使用CASE
:
select concat(first_name, " ", last_name) as CustomerName,
address,
case when address2 is null then 'None Provided' else address2 end as address2,
postal_code
from customer
inner join address
using (address_id);
甚至更好COALESCE
:
select concat(first_name, " ", last_name) as CustomerName,
address,
COALESCE(address2,'None Provided') as address2,
postal_code
from customer
inner join address
using (address_id);
如果数据包含一个空字符串或null,那么我会考虑使用:
select concat(first_name, " ", last_name) as CustomerName,
address,
case
when address2 is null or address2 = ''
then 'None Provided'
else address2 end as address2,
postal_code
from customer
inner join address
using (address_id);
答
您可以使用内置的COALESCE
函数来做到这一点。
返回列表中的第一个非NULL值,如果没有 非NULL值,则返回NULL。
select concat(first_name, " ", last_name) as CustomerName, address,
COALESCE(address2, 'None provided') as address2, postal_code
from customer
inner join address
using (address_id);
+0
我试过使用Coalesce和IfNull函数,但没有显示在我的结果中,只是地址2下的空字段。我认为,出于某种原因,它不认为该字段为空,而是认为它们有空间。是否有可能告诉MySql: 如果address2 =“”那么“没有提供”? – TaraX 2013-03-05 18:34:11
答
select concat(first_name, " ", last_name) as CustomerName, address,
COALESCE(address2,'none provided') as address2, postal_code
from customer
inner join address
using (address_id);
答
使用COALESCE ...
select concat(first_name, " ", last_name) as CustomerName, address, COALESCE(address2, 'None provided') as address2, postal_code from customer inner join address using (address_id);
COALESCE将在列表中
有没有更好的办法返回第一个非NULL值。你需要比较两个值。 – 2013-03-05 18:57:25