Thread: how to conditionally append

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_

kvnsmnsn@cs.byu.edu <kvnsmnsn@cs.byu.edu> schrieb:
> 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

... coalesce(ext,'')...

Read the doc about coalesce().



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

kvnsmnsn@cs.byu.edu wrote:
> Hello all,
>
>      I have a table that stores different parts of phone numbers...
> ...snip...
> 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.

Yes, this is expected. Since we are in the novice area, here are some tips:

Use the mental-model of null meaning "an unknown or unavailable value"
and the behavior of SQL will make more sense:

35 + some_unknown_value = some_unknown_value

Null does not equal null. (Q. does some_unknown_value equal
some_unknown_value? A. Unknown.) You can ask "...where x is null..." but
not " x = null". (OK, you can ask either but only one is useful.)

Null is not zero. Zero is a known value of zero.

Null is not ''. '' is a string with a known value of no characters. In
your example, ext is null might mean that you don't know if there is an
extension or you don't know what it is while ext = '' would mean that
you know that there is not an extension associated with this phone number.

As mentioned elsewhere, the coalesce function will allow you to
substitute a value for null:
npa || nxx || number || coalesce(ext, '')

The fact that you are using lpad on the npa and nxx indicates that there
is room for improvement in your database structure as it is impossible
to have a 1 or 2 digit npa or nxx (0=operator and 1=start of
long-distance number so npa and nxx must start with 2 or above hence
cannot, even as an int, be 2 digits).

I recommend using char(3), char(3), char(4). While the values consist of
strings of digits, they aren't numbers (ie. think of the operations to
be performed. I'm guessing that you won't be doing numeric operations
like adding the npa to the nxx or asking to sum or avg them but you will
be doing string operations like concatenating them to show the full
phone number).

Using char(3) creates a 3-char limit which the int won't have by
default. From there you can make your constraints as sophisticated as
you want. For npa/nxx:

1. Must be full three digits: ~ '[0-9]{3}'

2. Better, three digits not starting with 0 or 1: ~ '[2-9][0-9]{2}'

3. Even better, add restrictions to eliminate reserved npa/nxx like 911.

4. Go all the way and make npa/nxx a foreign-key referencing a table
containing all valid assigned npa/nxx combinations. This data can be
obtained from a variety of sources.

Cheers,
Steve