Concatenate using || causes null

Database
Enthusiast

Concatenate using || causes null

I noticed that when I am using || to concatenate several fields, if one of them is null, the function returns null. Is there a way to return whatever values that are not null and ignor the null value?

for example:

select 'xyz-' || field1 || '=' || field2 '-' || field3 from tablename

if any of the fields is null, the returned results will be null. is there a function that would still return populated values?

2 REPLIES
Junior Contributor

Re: Concatenate using || causes null

This is Standard SQL behaviour, use COALESCE(field,'').

Enthusiast

Re: Concatenate using || causes null

Thank you.