Re: Conactenating text with null values

[snip]
> 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.
Avoiding the inner trims:

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

Looks a bit more understandable :-)

Cheers,
Csaba.

>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org