如何在sql查询的空白字段中显示文本？

Question

我必须从数据库中提取客户列表，但是如果第二个地址字段为空，我想显示文本“ None Provided”。这是我的查询：

    select concat(first_name, " ", last_name) as CustomerName, address, 
    address2, postal_code 
    from customer
    inner join address 
    using (address_id);

如果地址2为空白，我想显示“无提供”。我怎么做？我敢肯定答案非常简单，但是我脑子屁，在任何地方都找不到答案。

好的，这就是我最终要进行的工作：

    case when address2=" " or address2 is null then 'None Provided' 
    else address2 end as address2,

有没有更好的方法来实现这一目标？

Answer 1

您可以使用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);