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: stackoverflow.com/q/19942824/939860

Erwin Brandstetter
– Erwin Brandstetter

2020-07-23 23:42:11 +00:00
Commented Jul 23, 2020 at 23:42 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 23, 2020 at 23:42

GMB · Accepted Answer · 2020-07-23 21:09:53Z

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, 2020 at 21:09

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related