sql - Postgresql 仅连接非空字符串

Question

假设您在类似于下表的表中有一些数据......

line1               line2               line3       city        zipcode
1 Any Road          NULL                NULL        Big Apple   12345
The White House     Pennsylvania Avenue NULL        Washington  20500
10 Downing Street   NULL                NULL        London      SW1A 2AA

如何返回非空字段的值。以“白宫”为例，我想返回除 line3 之外的所有字段的值(因为它具有空值)...

The White House, Pennsylvania Avenue, Washington, 20500

相对于...

The White House, Pennsylvania Avenue, **,** Washington, 20500

我正在尝试将 ACE/JET/MS Access 查询转换为其 Postgresql 等效项。在 Access 中，我使用类似于 ...

Select
    IIF(line1 <> '', ', ' + line1, '')
    + IIF(line2 <> '', ', ' + line2, '')
    + IIF(line3 <> '', ', ' + line3, '')
    + IIF(city <> '', ', ' + city, '')
    + IIF(zipcode <> '', ', ' + zipcode, '') as CustDetails
From addresses

这正是我所需要的。但是，Postgres 似乎没有 IIF 条件。我想我需要使用“CASE WHEN THEN”，但我正在努力解决嵌套问题。例如....

SELECT 
 CASE 
     WHEN line1 <> '' THEN line1  || ', '
     WHEN line2 <> '' THEN line2  || ', '
     WHEN line3 <> '' THEN line3  || ', '
     WHEN line4 <> '' THEN line4
 END AS CustDetails
FROM addresses

只是返回...

custDetails
The White House,

如何嵌套 Case 语句以仅在我的结果中显示非空值？

Answer 1

使用 concat_ws()(concat“带分隔符”)它将正确处理空值:

SELECT concat_ws(',', line1, line2, line3, line4, city, zipcode) as cust_details
FROM addresses;

如果任何元素为空，则不会添加分隔符。

如果你需要它忽略空字符串，使用 nullif:

SELECT concat_ws(',', line1, nullif(line2,''), nullif(line3,''), nullif(line4,''), city, zipcode) as cust_details
FROM addresses;