Someone on this list provided me with a rather elegant solution to this a few weeks ago:
CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql; CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text); And I call it as: SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda) Deals quite neatly with the NULLs in some of the columns. HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Fri 11/19/2004 9:53 AM To: Don Drake; [EMAIL PROTECTED] Cc: Subject: Re: [SQL] NULLS and string concatenation On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote: > On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake <[EMAIL PROTECTED]> wrote: > > > > I was able to work around the problem by using COALESCE (and casting > > variables since it wants the same data types passed to it). > > This is what you should do. If you don't mind using a non-standard feature, another possibility would be to create an operator similar to || that COALESCEs NULLs into empty strings. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
