Postgresql concat columns and skip column if column in NULL or is empty string

Question

I am trying to concatenate a number of columns and skip the column if column is NULL OR empty in postgres. For example:

SELECT CONCAT(coalesce('a',''),
        '|',coalesce('b',''),
        '|',coalesce(NULL,''),
        '|',coalesce('',''),
        '|',coalesce('',''),
        '|',coalesce('c','')) AS finalstring;

Output : a|b||||c

Expected output : a|b|c

Related: https://stackoverflow.com/q/19942824/939860 – Erwin Brandstetter Jul 23 '20 at 23:42 — Erwin Brandstetter, Jul 23 '20 at 23:42

score 10 · Answer 1 · answered Jul 23 '20 at 21:09

10

Use concat_ws(), which ignores null values:

concat_ws('|', col1, col2, col3, ...)

If you want to ignore empty strings as well, then you can use nullif():

concat_ws('|', nullif(col1, ''), nullif(col2, ''), nullif(col3, ''), ...)

answered Jul 23 '20 at 21:09

GMB

216,147
25
84
135

Postgresql concat columns and skip column if column in NULL or is empty string

1 Answers1