Slick ;o) This goes in my tool kit...
On Friday 19 November 2004 03:03 pm, Gregory S. Williamson saith: > 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 -- Quote: 87 "The federal government has taken too much tax money from the people, too much authority from the states, and too much liberty with the Constitution." --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
