小编典典

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

sql

我必须从数据库中提取客户列表,但是如果第二个地址字段为空,我想显示文本“ 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,

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


阅读 414

收藏
2021-03-08

共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);
2021-03-08