| Author |
Topic |
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
 Posted - 2005-03-16 : 08:03:45
|
| hiWhats is the best way to store phone numbers? Integer ? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 08:05:29
|
i'd say varchar with a pattern check.Go with the flow & have fun! Else fight the flow  |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 08:06:45
|
| What type of pattern check ? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 08:15:40
|
| depends on your phone syntax...for example:(123) 458-55-2468'([1-9][1-9][1-9]) [1-9][1-9][1-9]-[1-9][1-9]-[1-9][1-9][1-9][1-9]'Go with the flow & have fun! Else fight the flow |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 08:19:34
|
| oh i see.very very nicethanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 08:53:25
|
| Do you need to handle international numbers?Kristen |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 09:05:33
|
| Yes, all number formats must be international |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 09:35:51
|
| Well I suppose you could store them all in +44 (0) 1234 567890 format. But the number of digits in the number will vary from country to country. And people's ideas of how a number should be represented in each country will vary ...Kristen |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 10:59:51
|
| I simply use numbers, no + or bracket signs.:-) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-16 : 11:09:31
|
| I've been having phone number format issues lately as well. Especially now-a-days with all kinds of formats around. Our db isn't like this but I was thinking it would be good to keep the number in several columns. ie:intlCode, exchange, number, ext. Something like that where it's is as granular as possible. My formatting functions are becoming too complex and we have queries that return portions of the phone number broken out. :(Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 11:15:16
|
| In very recent times our London numbers changed from 01... to 071... to 0271 ...and all the rest of our numbers changed from 0449 style to gain a 1 (which London wasn't using anymore, by that time!!) so 01449 ...So having the "0449" bit in a separate column would enable a global change to be made.But in all my dealings with international data of this type it seems that there are so many styles, and most of the data entry people don't know them, so they are only copy-typing what is in front of them - and not all of that is 100% to start with ...Seems like a big effort to crack unless you have an awful lot of numbers, and the vailidity of the data is very important. An International Directory Enquires Database might have a need though ... :-)Kristen |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 11:18:51
|
quote:
In very recent times our London numbers changed from 01... to 071... to 0271 ...and all the rest of our numbers changed from 0449 style to gain a 1 (which London wasn't using anymore, by that time!!) so 01449 ...So having the "0449" bit in a separate column would enable a global change to be made.But in all my dealings with international data of this type it seems that there are so many styles, and most of the data entry people don't know them, so they are only copy-typing what is in front of them - and not all of that is 100% to start with ...
its amazing how something so simple coudl appear so complex.I never looked at it that way |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 11:37:49
|
| And imagine businesses in London having their letterheaded paper reprinted twice is almost as few years ... and all their shop signs ... to say nothing of the cost of the television adverts to educate the public ... and all the little utilities that came out to autoMagically update telephone numbers in Outlook and all the other PIMsDaft beyond belief that they didn't do it in one step.Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-16 : 11:47:40
|
| It might be good to store the numbers in a single type of numeric column, say BIGINT, that is big enough to contain the digits for any possible phone number, and then have another column to indicate the display style that will be used. You could fomat the number in your front end application, or have a view or stored procedure to do the formatting. Of course, figuring out all the different formats that are required may be a lot of work. I think there is also the problem to deal with of if you are dialing from inside a the same country or dialing from another country. If you only have a few countries to deal with, the problem may not be too bad.CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 11:51:27
|
| [rant]you know something, all of that is simply ridicoluous. i mean phone number format... who cares!!!we have a varchar(50) column and the user can put "My ass stinks" if he wants. we don't care. that's the users' problem.there's no way you can handle everything and doing it is wate of time.[/rant]Go with the flow & have fun! Else fight the flow |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 11:51:41
|
| When i first heard Anderson consulting was changing thier name to Accenture and they talked about thc cost implication, (which i cant remember now, I thought it was outrageous then)I was in London, when the number was 071 and 081 for central and outer london respectively, and was on holiday again when it was changed to 0207 and 0208. i wonder whats next ... ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 11:59:51
|
| So its your fault, is it? How do we prevent you going on holiday again? !Spirit: I'm 100% with you. Some simple data checking maybe, but phone number data around the world is simply wild. Apart from my hypothetical International Directory Enquires Database.Kristen |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-03-16 : 11:59:52
|
| My colleague advicedvarchar(55)reasons:1) you can store formatting characters such as spaces and parentheses2) you can search using LIKE3) you never need to find SUM(phoneno) or AVG(phoneno) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-03-16 : 12:08:46
|
| Kristen, you mean something like:http://www.wtng.infoas i said use varcharwith an optional pattern check if you need it.Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 12:19:50
|
| Brilliant! Who would want to program against this, eh? And this is just for my own country:Area Code: 2-6 digitsSubscriber Number: 3-8 digitsSo basically those two could be pretty much anything!!And:"Area Codes of 3-6 digits are becoming 2-4 digits standard. Subscriber Numbers of 3-7 digits are becoming 6-8 digits standard."And they are changing the London Numbers again I read - "an additional 0203 will be introduced" - Fantastic!So whatever you do now will be out of date in a couple of months!Are we on consultancy rates Spirit?!!Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-16 : 12:20:12
|
| We gave up on international phones and just used a free form varhcar field.In the states, we break them up into area code, exchange and numberWhen the country is not US, those fields are NullThe free form field contains all numbers, and US ones are formatted.Brett8-) |
|
|
|
Next Page
|
Whats is the best way to store phone numbers?