There is an easy solution anyway, use coalesce to ensure you are never returning a null result for any components of the concat.
e.g. select 'some text, blah:' || coalesce(NULL, '') equates to 'some text, blah:' || '' hence 'some text, blah:' Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Stephan Szabo > Sent: Friday, November 19, 2004 2:04 PM > To: Don Drake > Cc: Richard Huxton; [EMAIL PROTECTED] > Subject: Re: [SQL] NULLS and string concatenation > > > > On Fri, 19 Nov 2004, Don Drake wrote: > > > On Fri, 19 Nov 2004 17:48:34 +0000, Richard Huxton > <[EMAIL PROTECTED]> wrote: > > > Don Drake wrote: > > > > select 'some text, should be null:'|| NULL > > > > > > > > This returns NULL and no other text. Why is that? I > wasn't expecting > > > > the "some text.." to disappear altogether. > > > > > > > > Is this a bug? > > > > > > No. Null is "unknown" if you append unknown (null) to a > piece of text, > > > the result is unknown (null) too. > > > > > > If you're using NULL to mean something other than > unknown, you probably > > > want to re-examine your reasons why. > > > > > > > I'm using NULL to mean no value. Logically, NULL is > unknown, I agree. > > > > I'm trying to dynamically create an INSERT statement in a function > > that sometimes receives NULL values. > > > > This is still strange to me. In Oracle, the same query would not > > replace the *entire* string with a NULL, it treats the NULL as a no > > value. > > Oracle has some incompatibilities with the SQL spec (at least > 92/99) wrt > NULLs and empty strings so it isn't a good comparison point. > The spec is > pretty clear that if either argument to concatenation is NULL > the output > is NULL. > > > I can't find in the documentation where string concatenation of any > > string and NULL is NULL. > > I'm not sure it does actually. I'd have expected to see some > general text > on how most operators return NULL for NULL input but a quick > scan didn't > find any. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index > scan if your > joining column's datatypes do not match > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
