如何在 sql 查询的空字段中显示文本? [英] How do I display text in an empty field in an sql query?

问题描述

我必须从数据库中提取客户列表，但如果第二个地址字段为空，我想显示文本无提供.这是我的查询:

I have to pull a customer list from a database, but if the second address field is empty I want to display the text None Provided. Here is my query:

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

如果 address2 为空，我想显示没有提供".我怎么做?我敢肯定答案非常简单，但我脑子有问题，在任何地方都找不到答案.

If address2 is blank, I want to display "none provided". How do I do that? I'm sure the answer is super simple, but I'm having a brain fart and I can't find the answer anywhere.

好的，这就是我最终的目的:

Ok, this is what I ended up going with:

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

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

Is there a better way to achieve this?

推荐答案

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

如果数据包含空字符串或空值，那么我会考虑使用:

If the data contains an empty string or null, then I would consider using:

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

这篇关于如何在 sql 查询的空字段中显示文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！