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

how to conditionally append

From: kvnsmnsn(at)cs(dot)byu(dot)edu
To: pgsql-novice(at)postgresql(dot)org
Subject: how to conditionally append
Date: 2007-04-25 16:23:12
Message-ID: 63128.67.137.192.66.1177518192.squirrel@mail.cs.byu.edu (view raw or flat)
Thread:
Lists: pgsql-novice
Hello all,

     I have a table that stores different parts of phone numbers in
different columns, namely <areacode> for the first three digits,
<exchangenumber> for the next three digits, <last4digits> for the next
four digits, and <ext> for any extension the phone number might have.
The first three are stored as <smallint>s and <ext> is stored as a
<varchar( 20)>.

     The suggested way to display the whole phone number is:

     SELECT
       (  lpad( areacode   , 3, '0') || lpad( exchangenumber, 3, '0')
       || lpad( last4digits, 4, '0') || trim( ext))
       as phone
     FROM
       input_table;

The problem with this is that sometimes <ext> is <NULL>, and apparent-
ly a <NULL> value concatenated with any kind of character string al-
ways results in a <NULL> value.  At least that's the results I've been
getting; every time I try this SQL command with <ext> as <NULL> I get
the empty string, even though I _know_ that the other three columns
have actual values.

     Is there any way I can write a <SELECT> statement that only ap-
pends <trim( ext)> to the other columns _if_ <ext> is not <NULL>, and
that only appends the first three columns together otherwise?  Any
pointers on this question would be greatly appreciated.

                                ---Kevin Simonson
"You'll never get to heaven, or even to LA,
if you don't believe there's a way."
from _Why Not_


Responses

pgsql-novice by date

Next:From: Andreas KretschmerDate: 2007-04-25 16:32:40
Subject: Re: how to conditionally append
Previous:From: Daniel T. StaalDate: 2007-04-25 15:57:57
Subject: Re: International Date formats

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