Skip site navigation (1) Skip section navigation (2)

Re: NULLS and string concatenation

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: "Michael Fuhr" <mike(at)fuhr(dot)org>, "Don Drake" <dondrake(at)gmail(dot)com>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: NULLS and string concatenation
Date: 2004-11-19 20:03:21
Message-ID: 71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)fuhr(dot)org]
Sent:	Fri 11/19/2004 9:53 AM
To:	Don Drake; pgsql-sql(at)postgresql(dot)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(at)gmail(dot)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




Responses

pgsql-sql by date

Next:From: Terry Lee TuckerDate: 2004-11-19 20:14:32
Subject: Re: NULLS and string concatenation
Previous:From: Tom LaneDate: 2004-11-19 20:01:42
Subject: Re: NULLS and string concatenation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group