Postgresql Concatenating only non null strings

Question

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?

score 10 · Accepted Answer · 2020-10-15 11:37:34Z

10

Use concat_ws() (concat "with separator") it will deal correctly with null values:

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

The separator will not be added if any of the elements is null.

If you need it to ignore empty strings, use nullif:

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

edited Oct 15, 2020 at 11:37

answered Jan 13, 2018 at 16:45

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Seva Golovanov Over a year ago

It ignores null arguments, but not empty strings (PG12).

Gordon Linoff · Accepted Answer · 2018-01-13 16:45:41Z

2

You are looking for concat_ws():

concat_ws(',', line1, line2, line3, city, zipcode)

answered Jan 13, 2018 at 16:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Johnny Over a year ago

Thanks Gordon, I appreciate your time and help. a_horse_with_no_name pipped you to the post with the same answer so I accepted that answer, but +1'd yours.

Gordon Linoff Over a year ago

@Johnny . . . You can accept whatever answer you like. And, a_horse_with_no_name certainly has a worthy answer. For the record, though, this answer appeared 12 seconds earlier. You can hold the mouse by the date (it now says "yesterday") and it will show the exact time.

Johnny Over a year ago

'You can hold the mouse by the date (it now says "yesterday") and it will show the exact time.' I really didn't know you could do that. For me your answer was listed below 'a_horse' and therefore I thought you had been 'pipped'. Thank you for taking the time to point that out to me and for doing it so politely.

Johnny Over a year ago

A a side point. I noticed that if the field 'line1' was null, the cancat_ws function fails. It also appears to be the case if there are 2 nulls together, ie, line2 and line3 = null.

Gordon Linoff Over a year ago

@Johnny . . . I learned that trick years ago from Aaron Bertrand. In that case, he had beat me to the answer ;)

Collectives™ on Stack Overflow

Postgresql Concatenating only non null strings

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related