471,573 Members | 1,100 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,573 software developers and data experts.

Phone # question

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.

Aug 9 '05 #1
6 1099
(wa********@yahoo.com) writes:
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?


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 9 '05 #2
>> 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'

Aug 10 '05 #3
Stu
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

Aug 10 '05 #4
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:
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'

.

Aug 10 '05 #5
On 9 Aug 2005 19:49:11 -0700, --CELKO-- wrote:

(snip)
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'


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)
Aug 10 '05 #6
Thanks for the input guys. Delimeters it will be then I think.
I appreciate your thoughts, thanks again.

Aug 10 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by joemono | last post: by
4 posts views Thread by William Morris | last post: by
7 posts views Thread by MuZZy | last post: by
4 posts views Thread by kron | last post: by
4 posts views Thread by Blue Streak | last post: by
1 post views Thread by =?Utf-8?B?bW9uaWNhX2ZyYXppZXI=?= | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.