Re: how to conditionally append

kvnsmnsn(at)cs(dot)byu(dot)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