Re: Conactenating text with null values

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

Gregory S. Williamson schrieb:
> 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
togetheras 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
itshould. 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
iam missing (and I've been back and forth through the manual) would be most welcome. 
>
> Thanks,
>
> Greg Williamson
> DBA (hah!)
> GlobeXplorer LLC
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org