Concat Multiple columns into one

Question

I would like to concat multiple columns into a single column. When a value is Null I wish not to show it but I do want to show the values that exist. I couldn't find an example stackoverflow, below is a table of result set I'm trying to get, however, in this case because the "email" is null the entire result set is null not sure why this is occurring. I apologize in advance for my table if it doesn't display properly I've been using senseful github to create my table(s) but when I copy and paste here it displays weird.

SELECT
    Id,
    'study_id:' || study_id ||' , ' || 'email:' || email ||' , ' || 'phone:' || phone AS "Longvalue",
FROM
    mv_itest mi

id	Longvalue
123	`study_id:123 , phone:123-123-1234`

This is the result set if I use concat, however, I do not wish to display the alias for phone if the value is null

+-----+-----------------------+--+ | id | longvalue | | +-----+-----------------------+--+ | 987 | study_id:456 , phone: | | +-----+-----------------------+--+

Does this answer your question? String concatenation with a null seems to nullify the entire string - is that desired behavior in Postgres? — Dai
– Dai, Commented Aug 14, 2022 at 17:42
ISO SQL's || operator immediately returns NULL if any of the operands IS NULL (yes, I think this painful-by-default design is another demonstration of the ISO SQL commitee's latent sadism) - the solution is to use CONCAT() instead, which behaves like you want. — Dai
– Dai, Commented Aug 14, 2022 at 17:44
Well the only issue with Concat is that if I hardcode the column alias it will display the alias but with no value. @Dai so it would be like study_id:123 , email: , phone:123-123-1234 and I'm trying not to show the value when is null. — CerealBox
– CerealBox, Commented Aug 14, 2022 at 17:50
Use concat() or concat_ws() and your problem is gone. postgresql.org/docs/current/functions-string.html — Frank Heikens
– Frank Heikens, Commented Aug 14, 2022 at 17:53
By the way, imho presentation should be done in the presentation layer, not in the database. — Frank Heikens
– Frank Heikens, Commented Aug 14, 2022 at 17:54

Cetin Basoz · Accepted Answer · 2022-08-14 18:05:17Z

1

Since you want something like a json result, you might do that using JSON functions (it is already explained concatting NULL would yield null):

select id,
       regexp_replace(
               jsonb_strip_nulls(
                       jsonb_build_object(
                               'study_id:', study_id,
                               'email:', email,
                               'phone:', phone
                           ))::text, '[{}"]', '', 'g') as "longvalue"
from mv_itest mi;

answered Aug 14, 2022 at 18:05

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Concat Multiple columns into one

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