Обсуждение: Concatenate fields
Hi all,
using PgSQL 7.0.3
I would like concatenate some fields to have all information in just
one field. I'm doing this below:
CREATE TABLE bill_2col AS
SELECT bill.bill_id,(trim(text(bill.bill_number)) || ' | ' || trim(text(provider.company)) || ' | ' ||
trim(to_char(bill.issue_date,'MM/DD/YY'))|| ' | ' || trim(to_char(bill.amount,'9999999.99')) || ' pts') AS billdesc
FROM bill, provider
WHERE bill.provider_id = provider.provider_id
ORDER BY bill.bill_id;
When it finds some empty field, it makes all the new field empty, no
matters
if the other are empty or not.
Here you have the result table
bill_id | billdesc
---------+---------------------------------------------------------------
0 | Unknown | Unknown | 01/01/00 | .00 pts 1 | 98018097 | SUMI Informática | 12/22/99 | 1823520.00 pts
2| 3 | 99018089 | PISTA CERO S.L | 12/01/99 | 1949380.00 pts 4 | 99018089 | DataSystem Informática | 12/01/99
|1949380.00 pts
5 | 6 |
Amanda Riera wrote: >I would like concatenate some fields to have all information in just >one field. I'm doing this below:> >CREATE TABLE bill_2col AS >SELECT bill.bill_id, > (trim(text(bill.bill_number)) || ' | ' || > trim(text(provider.company))|| ' | ' || > trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || > trim(to_char(bill.amount,'9999999.99'))|| ' pts') AS billdesc >FROM bill, provider >WHERE bill.provider_id = provider.provider_id>ORDER BY bill.bill_id; > >When it finds some empty field, it makes all the new field empty, no >matters>if the other are empty or not. In this case, empty means NULL. Any concatenation involving NULL returns NULL; this is according to the standard. Use COALESCE(field,'') to return an empty string if field is NULL, so that no NULLs go into the concatenation. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Is any one of you in trouble? He should pray. Is anyone happy? Let him singsongs of praise. Is any one of you sick? He should call the elders of the church to pray over him...The prayerof a righteous man is powerful and effective." James 5:13,14,16
I've tried it and it works well, thanks a lot Oliver. Amanda Oliver Elphick wrote: > Amanda Riera wrote: > >I would like concatenate some fields to have all information in just > >one field. I'm doing this below: > > > >CREATE TABLE bill_2col AS > >SELECT bill.bill_id, > > (trim(text(bill.bill_number)) || ' | ' || > > trim(text(provider.company)) || ' | ' || > > trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || > > trim(to_char(bill.amount,'9999999.99')) || ' pts') AS billdesc > >FROM bill, provider > >WHERE bill.provider_id = provider.provider_id > >ORDER BY bill.bill_id; > > > >When it finds some empty field, it makes all the new field empty, no > >matters > >if the other are empty or not. > > In this case, empty means NULL. Any concatenation involving NULL returns > NULL; this is according to the standard. > > Use COALESCE(field,'') to return an empty string if field is NULL, so > that no NULLs go into the concatenation. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight http://www.lfix.co.uk/oliver > PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Is any one of you in trouble? He should pray. Is > anyone happy? Let him sing songs of praise. Is any one > of you sick? He should call the elders of the church > to pray over him...The prayer of a righteous man is > powerful and effective." James 5:13,14,16