Re: Conactenating text with null values

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?
> ----------
>
> (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 ?

If the blank fields are null then Informix is wrong. String concatenated
with null gives null.

SELECT coalesce(s_house,'') || ' ' || coalesce(s_post_dir,'') ...

To be honest, if the address fields are blank then they should be set to
the empty string. They're not "unknown" they're empty.

--
Richard Huxton
Archonet Ltd