Conactenating text with null values

[Gregory S. Williamson]

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.

Thanks,

Greg Williamson
DBA (hah!)
GlobeXplorer LLC
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Oliver Elphick]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Michael Fuhr]

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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Kleiser]

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

Gregory S. Williamson schrieb:

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.

Thanks,

Greg Williamson
DBA (hah!)
GlobeXplorer LLC
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Csaba Nagy]

[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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Alvaro Herrera]

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)
---------------------------(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

[Csaba Nagy]

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

[snip]

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)

---------------------------(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