Обсуждение: concatenation issue ( 8.4 )

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 ?

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

On 18 Sep 2009, at 18:25, Raymond O'Donnell wrote:

> 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, '') ....


Or better yet (you won't get double spaces if any value is NULL):

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

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4ab3c88e11681661021018!