Обсуждение: concat strings but spaces

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

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

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