469,090 Members | 1,188 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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 1077
(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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.