Re: Conactenating text with null values - Mailing list pgsql-general

From Oliver Elphick
Subject Re: Conactenating text with null values
Date
Msg-id 1099649751.5467.78.camel@braydb
Whole thread Raw
In response to Conactenating text with null values  ("Gregory S. Williamson" <gsw@globexplorer.com>)
Responses Re: Conactenating text with null values
List pgsql-general
On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote:
> This is postgres 7.4 on a linux box ...
>
> I have driven myself to distraction trying to what ought to be easy.
>
> I have a table with house number, street direction, street name and
> street suffix as 4 columns. I want to paste them together as one text
> string for use by another application.
>
> 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?
> ----------
>
> (1 row)
>
> I have tried all manner of COALESCE and various trickeries. Nothing
> works. In Informix this works exactly as I think it should. Is
> Informix totally whack, or what ?
>
> I guess I will have to code a perl script to do this seemingly
> straightforward operation. Any suggestions as to what i am missing
> (and I've been back and forth through the manual) would be most
> welcome.

I presume the empty columns are NULL.  Anything concatenated with NULL
produces NULL.  You should probably define columns that can be blank as
NOT NULL DEFAULT ''

With your current data you should use:

SELECT TRIM(TRIM(TRIM(COALESCE(s_house,'') || ' ' ||
COALESCE(s_post_dir,'')) || ' ' || COALESCE(s_street ,'')) || ' ' ||
COALESCE(s_suffix,'')) FROM parcels WHERE s_pin = '1201703303520';

The TRIMs are to remove surplus spaces from inside the result string.



pgsql-general by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Conactenating text with null values
Next
From: Joel
Date:
Subject: Re: OS X Install