Re: concatenation issue ( 8.4 )

On 18/09/2009 16:52, Jonathan Vanasco wrote:
> I have a table with
> name_first
> name_middle
> name_last
>
> if i try concatenating as such:
> SELECT
> name_first || ' ' || name_middle || ' ' || name_last
> FROM
> mytable
> ;
>
> I end up with NULL as the concatenated string whenever any of the
> referred fields contain a NULL value
>
> I tried some text conversion and explicit casting , but that didn't work
>
> What am I doing wrong ?

Use the coalesce() function to ensure that you get non-null values, thus:

select
coalesce(name_first, '') || ' ' || coalesce (name_middle, '') ....

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------