Brian, If you do text, you of course will want to do lots of validation on the way in to get rid of any parentheses, periods, spaces, dashes, etc. that the user puts in, so that you can then standardize it.
Without advocating the original plan for number fields, I can at least tell you how to do what you wanted. If you have a table PHONENUMBERS: part1 part2 part3 401 555 1204 401 555 431 415 555 49 ....where the second and third rows should actually have a part 3 of "0431" and "0049", respectively, you can pull the appropriate value using this SQL: SELECT TO_CHAR(part3,'0000') FROM PHONENUMBERS ....or, for the full number (assuming that area codes and exchanges never start with 0) formatted as ###-###-####: SELECT part1 || '-' || part2 || '-' || TO_CHAR(part3,'0000') AS PhoneNumber FROM PHONENUMBERS Of course, this just returns text anyway. But if you wanted to some reason to store them numerically but have queries hold properly formatted text, that's the trick. Also, I don't know if this works outside of Oracle. HTH, Matthieu -----Original Message----- From: Coleman, Brian [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 12:40 PM To: CF-Talk Subject: RE: phone numbers in database That is true, they are all US numbers and the same type of digits. I doubt they'd ever do any kind of searches on them. I think I'll switch it to be 1 field of text and slightly change the way it's entered. Thanks All -----Original Message----- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 11:03 AM To: CF-Talk Subject: RE: phone numbers in database But then again, I guess he would have said there was a 4th field for a possible country code so you're probably right... It would sort fine either way. If they are all in fact 3, 3 and 4 digits you could still store them as numbers and format them accordingly with numberformat to put any leading 0's back in place Something like.... function phoneFormatter(e, p, n) { p = "(#numberformat(e, '999')#) #numberformat(p, '999')#-#numberformat(n, '9999')#"; return p; } (or you could probably format the numbers in your SQL) ......just another option. But, like DW said... if they are all the same number of digits... they will sort fine alphabetically and CF will have no problem seeing them as numbers if/when you need it to. ....:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -----Original Message----- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 11:41 AM To: CF-Talk Subject: RE: phone numbers in database If they are all US numbers that would most likely be true. .....:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 11:29 AM To: CF-Talk Subject: RE: phone numbers in database > The only issue with that would be sorting them numerically versus > alphabetically > > 0 > 1 > 10 > 2 > 20 > 3 > 30 > > Would be what youd get with these numbers alphabetically instead of > numerically... If they're phone numbers, I suspect they all have the same number of digits, so I don't see why that would be a problem. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228265 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
