13

I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new field, owner_addr where each of the above fields is concatenated with // between each of them.

But if one or more of the original columns is NULL, I don't want to concatenate // to the result column. So for instance, if owner_addr1 is 123 4th Avenue SE and owner_addr2 and owner_addr3 are NULL, then I want the result column to just be 123 4th Avenue SE, not 123 4th Avenue SE // // (which would happen if I just did CONCAT() with // between the NULL strings ... I only want to add // between non-NULL columns, or leave it out altogether if there is only one non-NULL column.

Is there an easy way to do this kind of conditional concatenation in Postgresql, where it leaves out empty lines? Or should I write a python script to do this?

Improve this question
1
  • 1
    Related answer that may be of help: stackoverflow.com/q/12310986/939860. Note the last part about concat_ws() (and concat()) having function volatility STABLE (not IMMUTABLE), which prohibits their direct use in expression indexes. Commented May 14, 2018 at 1:28

2 Answers 2

Reset to default
24

The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored:

select concat_ws('//', owner_addr1, owner_addr2, owner_addr3)

Test:

red=# select concat_ws('//', 'abc', null, null, 'xx', null, 'xyz', null) 
          as address;
   address    
--------------
 abc//xx//xyz
(1 row)
Improve this answer
2

Probably most obvious to use a CASE statement. There are 4 cases:

owner_addr2 IS NULL AND owner_addr3 IS NULL => ''       
owner_addr2 IS NULL AND owner_addr3 IS NOT NULL => '//' || owner_addr3
owner_addr2 IS NOT NULL AND owner_addr3 IS NULL => '//' || owner_addr2
owner_addr2 IS NOT NULL AND owner_addr3 IS NOT NULL '//' || owner_addr2 || '//' owner_addr3

SELECT owner_addr1 
    || CASE WHEN owner_addr2 IS NULL AND owner_addr3 IS NULL
            THEN ''
            WHEN owner_addr2 IS NULL AND owner_addr3 IS NOT NULL
            THEN '//' || owner_addr3
            WHEN owner_addr2 IS NOT NULL AND owner_addr3 IS NULL 
            THEN '//' || owner_addr2   
            WHEN owner_addr2 IS NOT NULL AND owner_addr3 IS NOT NULL 
            THEN '//' || owner_addr2 || '//' || owner_addr3
       END AS owner_addr
FROM ...

An alternative is to use 2 CASE statements:

SELECT owner_addr1 
    || CASE WHEN owner_addr2 IS NULL 
            THEN '' 
            ELSE '//' || owner_addr2
       END
    || CASE WHEN owner_addr3 IS NULL 
            THEN '' 
            ELSE '//' || owner_addr3
       END as owner_addr
FROM ...

COALESCE can be used instead of CASE:

SELECT owner_addr1 
    || COALESCE('//' || owner_addr2, '')
    || COALESCE('//' || owner_addr3, '') as owner_addr
FROM ...
Improve this answer
3
  • Note that the answer assumes that owner_addr1 is not null (the question seems to imply that, so it should be ok). Commented May 3, 2018 at 8:36
  • 1
    Yes, I assumed that. Gets more complicated if that is not the case. CONCAT_WS looks like a neat function, reminds me of LISP:-) Commented May 3, 2018 at 8:42
  • 1
    the coalesce form can be used the three terms the same and then a substring chopping of the start Commented May 3, 2018 at 9:48

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.