Re: Conactenating text with null values

On Fri, Nov 05, 2004 at 01:25:07AM -0800, Gregory S. Williamson wrote:

> SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin = '1201703303520';
>  s_house | s_post_dir |    s_street    | s_suffix
> ---------+------------+----------------+----------
>  34643   |            | FIG TREE WOODS |
>
> So to get "34643 FIG TREE WOODS" what do I do ?
>
> SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' || s_suffix FROM parcels WHERE s_pin = '1201703303520';
>  ?column?
> ----------
>

This query almost works:

SELECT COALESCE(s_house, '')    || ' ' ||
       COALESCE(s_post_dir, '') || ' ' ||
       COALESCE(s_street, '')   || ' ' ||
       COALESCE(s_suffix, '')
FROM parcels WHERE s_pin = '1201703303520';
        ?column?
------------------------
 34643  FIG TREE WOODS

However, the result has excess spaces where the NULL fields are.
You could use functions like REPLACE(), LTRIM(), and RTRIM() to
get rid of extra spaces, but it might be easier write a function
to build the address string from only the non-NULL components:

SELECT buildaddr(s_house, s_post_dir, s_street, s_suffix)
FROM parcels WHERE s_pin = '1201703303520';
      buildaddr
----------------------
 34643 FIG TREE WOODS

Here's a PL/Perl implementation of buildaddr():

CREATE OR REPLACE FUNCTION buildaddr(TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS '
return join(" ", grep {defined} @_);
' LANGUAGE plperl;

Here's a PL/pgSQL implementation; maybe somebody can improve on it:

CREATE OR REPLACE FUNCTION buildaddr(TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS '
DECLARE
    addr  TEXT[] := ''{}'';
BEGIN
    IF $1 IS NOT NULL THEN
        addr := array_append(addr, $1);
    END IF;

    IF $2 IS NOT NULL THEN
        addr := array_append(addr, $2);
    END IF;

    IF $3 IS NOT NULL THEN
        addr := array_append(addr, $3);
    END IF;

    IF $4 IS NOT NULL THEN
        addr := array_append(addr, $4);
    END IF;

    RETURN array_to_string(addr, '' '');
END;
' LANGUAGE plpgsql;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/