Re: Conactenating text with null values

Sweet. I learn something every day. thanks for ideas, one and all!
G
-----Original Message-----
From:    Alvaro Herrera [mailto:alvherre@dcc.uchile.cl]
Sent:    Fri 11/5/2004 8:49 AM
To:    Csaba Nagy
Cc:    olly@lfix.co.uk; Gregory S. Williamson; Postgres general mailing list
Subject:    Re: [GENERAL] Conactenating text with null values
On Fri, Nov 05, 2004 at 12:51:11PM +0100, Csaba Nagy wrote:
> [snip]
> > SELECT TRIM(TRIM(TRIM(COALESCE(s_house,'') || ' ' ||
> > COALESCE(s_post_dir,'')) || ' ' || COALESCE(s_street ,'')) || ' ' ||
> > COALESCE(s_suffix,'')) FROM parcels WHERE s_pin = '1201703303520';
> >
> > The TRIMs are to remove surplus spaces from inside the result string.
> Avoiding the inner trims:
>
> SELECT TRIM(
>   COALESCE(s_house || ' ','')
>   || COALESCE(s_post_dir || ' ','')
>   || COALESCE(s_street || ' ','')
>   || COALESCE(s_suffix,'')
> ) FROM parcels WHERE s_pin = '1201703303520';
>
> Looks a bit more understandable :-)

But it's still too cumbersome.  How about creating a new operator?  With
the example below the query would simply be

SELECT s_host ||~ s_post_dir ||~ s_street ||~ s_suffix
  FROM parcels
 WHERE s_pin = '1201703303520';

alvherre=# CREATE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) RETURNS text AS 'SELECT CASE WHEN $1 IS
NULLTHEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql; 
CREATE FUNCTION
alvherre=# SELECT text_concat_nulls_with_an_embedded_space('foo', NULL);
 text_concat_nulls_with_an_embedded_space
------------------------------------------
 foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space(NULL, 'foo');
 text_concat_nulls_with_an_embedded_space
------------------------------------------
 foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space('bar', 'foo');
 text_concat_nulls_with_an_embedded_space
------------------------------------------
 bar foo
(1 fila)
alvherre=# CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR
alvherre=# SELECT 'hi' ||~ 'foo' ||~ null ||~ null ||~ 'bar' ||~ 'baz' ||~ null ||~ 'bye';
      ?column?
--------------------
 hi foo bar baz bye
(1 fila)


--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The first of April is the day we remember what we are
the other 364 days of the year"  (Mark Twain)