samedi 13 janvier 2018

Postgresql Concatenating only non null strings

Imagine you have some data in a table similar to the following ...

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

How can I return values for the fields that are not null. Taking 'The White House' as an example I would like to return values for all fields except line3 (as that has a null value) ...

The White House, Pennsylvania Avenue, Washington, 20500

as opposed to ...

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

I am trying to convert an ACE/JET/MS Access query into its Postgresql equivalent. In Access I use an IIF similar to ...

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

This would give me exactly what I need. However, Postgres does not appear to have an IIF conditional. I think I need to use 'CASE WHEN THEN' but I am struggling to get my head around nesting things. For example ....

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

just returns ...

custDetails
The White House,

How can I nest a Case statement to only show non null values in my results?

Aucun commentaire:

Enregistrer un commentaire