Conactenating text with null values

Gregory S. Williamson wrote:

SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin
= '1201703303520'; s_house | s_post_dir | s_street | s_suffix
---------+------------+----------------+---------- 34643 |
| FIG TREE WOODS |

So to get "34643 FIG TREE WOODS" what do I do ?

SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' ||
s_suffix FROM parcels WHERE s_pin = '1201703303520'; ?column?
----------

(1 row)

I have tried all manner of COALESCE and various trickeries. Nothing
works. In Informix this works exactly as I think it should. Is
Informix totally whack, or what ?

If the blank fields are null then Informix is wrong. String concatenated
with null gives null.

SELECT coalesce(s_house,'') || ' ' || coalesce(s_post_dir,'') ...

To be honest, if the address fields are blank then they should be set to
the empty string. They're not "unknown" they're empty.

--
Richard Huxton
Archonet Ltd

On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote:

This is postgres 7.4 on a linux box ...

I have driven myself to distraction trying to what ought to be easy.

I have a table with house number, street direction, street name and
street suffix as 4 columns. I want to paste them together as one text
string for use by another application.

SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin =
'1201703303520';
s_house | s_post_dir | s_street | s_suffix
---------+------------+----------------+----------
34643 | | FIG TREE WOODS |

So to get "34643 FIG TREE WOODS" what do I do ?

SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' ||
s_suffix FROM parcels WHERE s_pin = '1201703303520';
?column?
----------

(1 row)

I have tried all manner of COALESCE and various trickeries. Nothing
works. In Informix this works exactly as I think it should. Is
Informix totally whack, or what ?

I guess I will have to code a perl script to do this seemingly
straightforward operation. Any suggestions as to what i am missing
(and I've been back and forth through the manual) would be most
welcome.

I presume the empty columns are NULL. Anything concatenated with NULL
produces NULL. You should probably define columns that can be blank as
NOT NULL DEFAULT ''

With your current data you should use:

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.

Thanks to you and Richard for pointing me in the right direction (I had the wrong syntax on the coalesce function) ... too late at night here (too early in the morning?) and I much appreciate the help.

The mysteries of NULL ...

Greg W.

-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: Fri 11/5/2004 2:15 AM
To: Gregory S. Williamson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Conactenating text with null values
On Fri, 2004-11-05 at 09:25, Gregory S. Williamson wrote:

This is postgres 7.4 on a linux box ...

I have driven myself to distraction trying to what ought to be easy.

I have a table with house number, street direction, street name and
street suffix as 4 columns. I want to paste them together as one text
string for use by another application.

SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin =
'1201703303520';
s_house | s_post_dir | s_street | s_suffix
---------+------------+----------------+----------
34643 | | FIG TREE WOODS |

So to get "34643 FIG TREE WOODS" what do I do ?

SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' ||
s_suffix FROM parcels WHERE s_pin = '1201703303520';
?column?
----------

(1 row)

I have tried all manner of COALESCE and various trickeries. Nothing
works. In Informix this works exactly as I think it should. Is
Informix totally whack, or what ?

I guess I will have to code a perl script to do this seemingly
straightforward operation. Any suggestions as to what i am missing
(and I've been back and forth through the manual) would be most
welcome.

I presume the empty columns are NULL. Anything concatenated with NULL
produces NULL. You should probably define columns that can be blank as
NOT NULL DEFAULT ''

With your current data you should use:

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.

On Fri, Nov 05, 2004 at 01:25:07AM -0800, Gregory S. Williamson wrote:

SELECT s_house,s_post_dir,s_street,s_suffix FROM parcels WHERE s_pin = '1201703303520';
s_house | s_post_dir | s_street | s_suffix
---------+------------+----------------+----------
34643 | | FIG TREE WOODS |

So to get "34643 FIG TREE WOODS" what do I do ?

SELECT s_house || ' ' || s_post_dir || ' ' || s_street || ' ' || s_suffix FROM parcels WHERE s_pin = '1201703303520';
?column?
----------

This query almost works:

SELECT COALESCE(s_house, '') || ' ' ||
COALESCE(s_post_dir, '') || ' ' ||
COALESCE(s_street, '') || ' ' ||
COALESCE(s_suffix, '')
FROM parcels WHERE s_pin = '1201703303520';
?column?
------------------------
34643 FIG TREE WOODS

However, the result has excess spaces where the NULL fields are.
You could use functions like REPLACE(), LTRIM(), and RTRIM() to
get rid of extra spaces, but it might be easier write a function
to build the address string from only the non-NULL components:

SELECT buildaddr(s_house, s_post_dir, s_street, s_suffix)
FROM parcels WHERE s_pin = '1201703303520';
buildaddr
----------------------
34643 FIG TREE WOODS

Here's a PL/Perl implementation of buildaddr():

CREATE OR REPLACE FUNCTION buildaddr(TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS '
return join(" ", grep {defined} @_);
' LANGUAGE plperl;

Here's a PL/pgSQL implementation; maybe somebody can improve on it:

CREATE OR REPLACE FUNCTION buildaddr(TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS '
DECLARE
addr TEXT[] := ''{}'';
BEGIN
IF $1 IS NOT NULL THEN
addr := array_append(addr, $1);
END IF;

IF $2 IS NOT NULL THEN
addr := array_append(addr, $2);
END IF;

IF $3 IS NOT NULL THEN
addr := array_append(addr, $3);
END IF;

IF $4 IS NOT NULL THEN
addr := array_append(addr, $4);
END IF;

RETURN array_to_string(addr, '' '');
END;
' LANGUAGE plpgsql;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

SELECT COALESCE(s_house,'') || COALESCE(s_post_dir,'') || COALESCE(s_street,'') || COALESCE(s_suffix,'') FROM parcels WHERE s_pin = '1201703303520';

Gregory S. Williamson schrieb:

[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 :-)

Cheers,
Csaba.

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 NULL THEN $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)

Cool, this goes to my "util" mail folder :-)

[snip]

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 NULL THEN $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)