pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Concatenate fields
3+ messages / 2 participants
[nested] [flat]

* Concatenate fields
@ 2001-04-11 11:31  Amanda Riera <amanda@labtie.mmt.upc.es>
  0 siblings, 1 reply; 3+ messages in thread

From: Amanda Riera @ 2001-04-11 11:31 UTC (permalink / raw)
  To: pgsql-sql


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 |







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Concatenate fields
@ 2001-04-11 12:14  Oliver Elphick <olly@lfix.co.uk>
  parent: Amanda Riera <amanda@labtie.mmt.upc.es>
  0 siblings, 1 reply; 3+ messages in thread

From: Oliver Elphick @ 2001-04-11 12:14 UTC (permalink / raw)
  To: Amanda Riera <amanda@labtie.mmt.upc.es>; +Cc: pgsql-sql

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 





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Concatenate fields
@ 2001-04-18 08:28  Amanda Riera <amanda@labtie.mmt.upc.es>
  parent: Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Amanda Riera @ 2001-04-18 08:28 UTC (permalink / raw)
  To: Oliver Elphick <olly@lfix.co.uk>; pgsql-sql

  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








^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-04-18 08:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-11 11:31 Concatenate fields Amanda Riera <amanda@labtie.mmt.upc.es>
2001-04-11 12:14 ` Oliver Elphick <olly@lfix.co.uk>
2001-04-18 08:28   ` Amanda Riera <amanda@labtie.mmt.upc.es>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox