Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3BCEU845870 for ; Wed, 11 Apr 2001 08:14:30 -0400 (EDT) (envelope-from olly@lfix.co.uk) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 2.12 #1) id 14nJVy-000CBN-0V; Wed, 11 Apr 2001 13:14:26 +0100 Received: from lfix.co.uk (olly@localhost [127.0.0.1]) by localhost (8.11.3/8.11.3/Debian 8.11.2-1) with ESMTP id f3BCEP205618; Wed, 11 Apr 2001 13:14:25 +0100 Message-Id: <200104111214.f3BCEP205618@linda.lfix.co.uk> X-Mailer: exmh version 2.3.1 01/18/2001 (debian 2.3.1-1) with nmh-1.0.4+dev X-URL: http://www.lfix.co.uk/oliver X-face: "xUFVDj+ZJtL_IbURmI}!~xAyPC"Mrk=MkAm&tPQnNq(FWxv49R}\>0oI8VM?O2VY+N7@F- KMLl*!h}B)u@TW|B}6 cc: pgsql Subject: Re: Concatenate fields In-reply-to: Message from Amanda Riera of Wed, 11 Apr 2001 13:31:05 +0200. <3AD44079.FCFE4583@labtie.mmt.upc.es> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Date: Wed, 11 Apr 2001 13:14:24 +0100 From: "Oliver Elphick" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f3BCEU845871 X-Archive-Number: 200104/154 X-Sequence-Number: 2502 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