Phone # question

[wackyphill@yahoo.com]

I assume a SS# or Phone# should be either a varchar or char field. But
is it a good idea to store a phone# or SS# w/ seperator characters or
without them?

Phone# "(123)-456-7890" or "1234567890 "

SS# "123-45-6789" or "123456789"

I'm not sure which is better. Any opinions? Also has anyone had a
situation where a phone# could have an extension? How was it handled?
Seperate field or just one?

Any input appreciated. Thanks.

[Erland Sommarskog]

Re: Phone # question

(wackyphill@yah oo.com) writes:[color=blue]
> I assume a SS# or Phone# should be either a varchar or char field. But
> is it a good idea to store a phone# or SS# w/ seperator characters or
> without them?
>
> Phone# "(123)-456-7890" or "1234567890 "
>
> SS# "123-45-6789" or "123456789"
>
> I'm not sure which is better. Any opinions? Also has anyone had a
> situation where a phone# could have an extension? How was it handled?
> Seperate field or just one?[/color]

For Phone numbers I would recommend delimiters, or else a number
like +46-8-90510 will be mangled.

For social-security numbers it's a little different. We store them
without delimiters, but I wonder what we would do if would had to
handle numbers like the above. Then again, we already store the
country code associated with SSN, so maybe it would not be a problem.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Phone # question

>> SS# or Phone# should be either a varchar or char field [sic] <<

Try use CHAR(n) instead of VARCHAR(n) instead. I like removing the
delimiters IF the format is fixed. If the data can have various
formats, then use a horrible constraint:

CREATE TABLE Foobar
( ..
x CHAR(3) DEFAULT ('XXX') NOT NULL
CHECK (CASE WHEN x LIKE 'AB[0-9]' THEN 'T'
WHEN x LIKE 'XY[0-9]' THEN 'T'
\\ ELSE 0 END = 'T'

[Stu]

Re: Phone # question

I agree with Erland; I would let the application validate the phone
number and enter them with delimiters into your database, mainly
because of issues with international numbers and extensions. We
typically enter phone + extension into a single column in our database
structure, and extract the partial phone # (if necessary) on the client
side.

HTH,
Stu

[Steve Kass]

Re: Phone # question

The constraint is horrible, that's for sure. It
raises an error trying to convert 'T' to a number,
since the CASE expression will be typed as an integer
or numeric.

What integer did 'T' convert to on the system
you used to test the code?


If you need a constraint like this, write it so
it can be understood, and so that it doesn't
generate an error:

CHECK (x LIKE 'AB[0-9]' OR x LIKE 'XY[0-9]')

Steve Kass
Drew University


--CELKO-- wrote:[color=blue][color=green][color=darkred]
>>>SS# or Phone# should be either a varchar or char field [sic] <<[/color][/color]
>
>
> Try use CHAR(n) instead of VARCHAR(n) instead. I like removing the
> delimiters IF the format is fixed. If the data can have various
> formats, then use a horrible constraint:
>
> CREATE TABLE Foobar
> ( ..
> x CHAR(3) DEFAULT ('XXX') NOT NULL
> CHECK (CASE WHEN x LIKE 'AB[0-9]' THEN 'T'
> WHEN x LIKE 'XY[0-9]' THEN 'T'
> \\ ELSE 0 END = 'T'
>
> .
>[/color]

[Hugo Kornelis]

Re: Phone # question

On 9 Aug 2005 19:49:11 -0700, --CELKO-- wrote:

(snip)[color=blue]
> x CHAR(3) DEFAULT ('XXX') NOT NULL
> CHECK (CASE WHEN x LIKE 'AB[0-9]' THEN 'T'
> WHEN x LIKE 'XY[0-9]' THEN 'T'
>\\ ELSE 0 END = 'T'[/color]

Hi Joe,

Apart from Steve's comments, I'm also curious why you supply a default
that won't pass the CHECK constraint. If you want an error if no value
is supplied, then why not simply remove the DEFAULT? Gives me an error
message that is much easier to debug!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[wackyphill@yahoo.com]

Re: Phone # question

Thanks for the input guys. Delimeters it will be then I think.
I appreciate your thoughts, thanks again.