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_
