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@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------