Postgres Pro
Downloads
Home   >   mailing lists

Thread: Conactenating text with null values

Conactenating text with null values

From
"Gregory S. Williamson"
Date:
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
togetheras 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
ammissing (and I've been back and forth through the manual) would be most welcome. 

Thanks,

Greg Williamson
DBA (hah!)
GlobeXplorer LLC

Re: Conactenating text with null values

From
Richard Huxton
Date:
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

Re: Conactenating text with null values

From
Oliver Elphick
Date:
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.

Re: Conactenating text with null values

From
"Gregory S. Williamson"
Date:
Thanks to you and Richard for pointing me in the right direction (I had the wrong syntax on the coalesce function) ...
toolate 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.

Re: Conactenating text with null values

From
Michael Fuhr
Date:
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/

Re: Conactenating text with null values

From
Michael Kleiser
Date:
SELECT COALESCE(s_house,'') || COALESCE(s_post_dir,'') || COALESCE(s_street,'') || COALESCE(s_suffix,'') FROM parcels
WHEREs_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
togetheras 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
itshould. 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
iam 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

Re: Conactenating text with null values

From
Csaba Nagy
Date:
[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

Re: Conactenating text with null values

From
Alvaro Herrera
Date:
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)

Re: Conactenating text with null values

From
Csaba Nagy
Date:
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
ISNULL 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)
>

Re: Conactenating text with null values

From
"Gregory S. Williamson"
Date:
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)