From: Terry Lee Tucker <terry@esc1.com>
To: pgsql-sql@postgresql.org
Subject: Re: NULLS and string concatenation
Date: Fri, 19 Nov 2004 15:14:32 -0500
Message-ID: <200411191514.32896.terry@esc1.com> (raw)
In-Reply-To: <71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net>
References: <71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net>
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:mike@fuhr.org]
> Sent: Fri 11/19/2004 9:53 AM
> To: Don Drake; pgsql-sql@postgresql.org
> 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 <dondrake@gmail.com>
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: terry@esc1.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terry@esc1.com
Subject: Re: NULLS and string concatenation
In-Reply-To: <200411191514.32896.terry@esc1.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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