From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | rod(at)iol(dot)ie |
Cc: | Jonathan Vanasco <postgres(at)2xlp(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: concatenation issue ( 8.4 ) |
Date: | 2009-09-18 17:51:06 |
Message-ID: | 1B3FB908-2D69-476F-B0D3-2ED7CF852437@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2009-09-18 19:16:05 | Re: NAS |
Previous Message | ekekakos | 2009-09-18 16:44:51 | Repetitive fields |