concat strings but spaces

Matthias Teege wrote:

Moin,

I try to concat values from three fields in a function like this:

create or replace function
fconcat_name(varchar, varchar, varchar) returns varchar as '
declare
ttl alias for $1;
vnm alias for $2;
nme alias for $3;
begin
return ttl || '' '' || vnm || '' '' || nme;
end;
' language plpgsql;

That works but if one of the fields is empty there are to much
spaces in the result. Is there any alternative to a monster
if-then construct?

Many thanks
Matthias

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

There are a few ways to do this, one is by using the COALESCE function
which returns the first of its arguments that is not null.
eg
select coalesce('123' || ' ' || 456, null);
coalesce
----------
123 456
(1 row)

You could have a few COALESCE calls, or use a CASE expression, or use IF
statements.

Ron

Matthias Teege wrote:

I try to concat values from three fields in a function like this:

create or replace function
fconcat_name(varchar, varchar, varchar) returns varchar as '
declare
ttl alias for $1;
vnm alias for $2;
nme alias for $3;
begin
return ttl || '' '' || vnm || '' '' || nme;
end;
' language plpgsql;

That works but if one of the fields is empty there are to much
spaces in the result. Is there any alternative to a monster
if-then construct?

Maybe like this:

create or replace function
fconcat_name(varchar, varchar, varchar) returns varchar as '
select replace($1 || '' '' || $2 || '' '' || $3, '' '', '' '')
' language sql;

regression=# select fconcat_name('John','','Doe');
fconcat_name
--------------
John Doe
(1 row)

HTH,

Joe