List:General Discussion« Previous MessageNext Message »
From:Rhino Date:November 19 2005 10:31pm
Subject:Re: Mysql Finding the country name from country prefix
View as plain text  
----- Original Message ----- 
From: "Michael Stassen" <Michael.Stassen@stripped>
To: "Rhino" <rhino1@stripped>
Cc: "abhishek jain" <mail@stripped>; <mysql@stripped>
Sent: Saturday, November 19, 2005 3:55 PM
Subject: Re: Mysql Finding the country name from country prefix


> Rhino wrote:
>>
>> ----- Original Message ----- From: "Michael Stassen" 
>> <Michael.Stassen@stripped>
>> To: "Rhino" <rhino1@stripped>
>> Cc: "abhishek jain" <mail@stripped>; <mysql@stripped>
>> Sent: Saturday, November 19, 2005 1:11 PM
>> Subject: Re: Mysql Finding the country name from country prefix
>>
>>
>>> Rhino wrote:
>>>
>>>>
>>>> ----- Original Message ----- From: "abhishek jain"
> <mail@stripped>
>>>> To: <mysql@stripped>
>>>> Sent: Saturday, November 19, 2005 8:10 AM
>>>> Subject: Mysql Finding the country name from country prefix
>>>>
>>>>
>>>>> Hi Friends,
>>>>> I have a ticky mysql problem.
>>>>> I need to find the country name from the country prefix so eg. i have
> 
>>>>> a
>>>>> mysql table with prefix and name as the coumns and with datas like :
>>>>> Prefix,  Name
>>>>> 1          USA
>>>>> 11        XYZ
>>>>> 44        UK
>>>>> 91        India
>>>>> 92        Pakistan
>>>>> 123      ZXF
>>>>> and i have a number like 911122334455 and i need t match that to 
>>>>> india.
>>>>> I cannt do that directly by this statement select name from
>>>>> country_table where prefix='91'; for the simple reason as i do not 
>>>>> have
>>>>> the length or the no. of digits the prefix is beforehand.
>>>>> Pl. help me out.
>>>>> Quick help will be appreciated.
>>>>
>>>>
>>>> You are not explaining your problem very clearly at all, particularly 
>>>> why you think this is a MySQL problem.
>>>>
>>>> It looks to me as if you are trying to parse a phone number and 
>>>> determine from the first few digits what country that phone number 
>>>> represents.  You already have a table that gives you the country code 
>>>> for each country and it shows clearly that 91 is the country code for 
>>>> India. As for the length of the country code, surely you can easily 
>>>> calculate that 91 has two digits in it.
>>>>
>>>> If you are saying that you have only a string of digits and need to 
>>>> determine the country in which that phone number originates without any 
>>>> further information, all I can say is good luck. The fact is that 
>>>> people write their phone numbers in many different ways. Here in 
>>>> Canada, if I'm giving my number to someone who lives within my city or 
>>>> region, I'm likely to give them only the last seven digits, e.g. 
>>>> 5551212. If I wanted to give my number to someone farther away in 
>>>> Canada, the US, or the Caribbean, I'd give them 5195551212 since we all 
>>>> share the same country code, 1. If I wanted to give my number to 
>>>> someone in some other foreign country, I'd give them 15195551212. So, 
>>>> right away, you have three different ways to express the phone number 
>>>> all of which are accurate and complete in their own context. If you 
>>>> parsed the first example, you might assume that I am in Brazil, because 
>>>> '55' is the country code for Brazil. (Country codes '5' and '555' are 
>>>> not in use at present.) If you parsed 519-555-1212, you wouldn't find 
>>>> anything because there is no country code '5', '51', or '519' currently 
>>>> in use. If you tried to parse '15195551212', you'd think I was in the 
>>>> US, Canada, or one of the Caribbean countries since '1' is the code for 
>>>> those countries. (There is is no '15' or '151' country code at 
>>>> present.) Therefore, the phone number _by itself_ is next to useless to 
>>>> you unless you are absolutely certain that the phone number is complete 
>>>> and includes the country code, area/city code and local number.
>>>>
>>>> None of that is a MySQL problem. The problem lies in your data 
>>>> acquisition technique. If you have to parse phone numbers, the input 
>>>> forms you use have to ensure that the user supplies the entire phone 
>>>> number; ideally, that number would be supplied in different fields, one 
>>>> of which would be the country code. Then you would have no problem 
>>>> except making sure that the user has supplied their own phone number 
>>>> and not someone elses. (The number I used in my examples, 15195551212, 
>>>> is the directory assistance phone number for my area, not my own phone 
>>>> number.)
>>>>
>>>> So, unless I've misunderstood what you are asking, I don't think we can 
>>>> help you very much. There is no function in MySQL or any other database 
>>>> I have used that can calculate the country code accurately given only a 
>>>> phone number that may or may not be complete.
>>>>
>>>> Rhino
>>>
>>>
>>> Everything you say is true, if the list contains incomplete phone 
>>> numbers, but why do you assume that is the case?  The OP said no such 
>>> thing.  The question is, given a string such as '911122334455', how do 
>>> you find rows in the country_table where the prefix column matches the 
>>> beginning of the string?  I think that amounts to, how do I do string 
>>> comparisons in mysql?
>>>
>> Actually, it is the _original poster_ that is assuming the phone number 
>> is complete; I'm just trying to warn him that the problem becomes nearly 
>> insoluble if the phone number _isn't_ complete.
>
> He may not be *assuming*, he may know, but okay.
>
You're right; he may be 100% certain that the numbers are complete. Of 
course that doesn't mean they _are_ 100% complete; he may just _think_ they 
are :-)

I certainly wouldn't expect them to be complete; I've never seen a form that 
asked me for a phone number, failed to ask for my country, and then verified 
somehow that my phone number included a country code, let alone did edits to 
make sure it was the right country code. (How could it verify the country 
code was correct if I didn't supply a country name? Even if the country name 
_was_ supplied, you could only easily do a partial verification: you could 
verify that the country code was an existing country code, not an imaginary 
one, and that it was appropriate for the country they chose, e.g. they used 
country code 1 for the US. But you still couldn't be sure the person was 
entering their _own_ phone number or country name for that matter.)

I hate giving out my personal information because it is sometimes sold to 
telemarketers, even if the organization to which you gave the information 
swore they wouldn't do that. So, unless it is an organization I trust and 
they can make a convincing case for why they need my phone number, mailing 
address, or whatever, I won't give it to them. And if they insist on it 
anyway, I have no hesitation in just making up a street address, postal 
code, and phone number, just to get them off my back. If the original poster 
is getting his data from anyone that I've dealt with, he is almost certainly 
getting a bogus information, at least from me. I would be highly surprised 
if I was the only one who'd ever thought of this strategy to avoid 
telemarketers; if I'm right, a good percentage of data the original poster 
has may be highly inaccurate.

>> Even if the number is complete, if we don't know the country associated 
>> with the phone number, which is the whole problem, how many digits of the 
>> number are the country code if the country code can be 1 thru 4 digits?
>
> You don't need to know the number of digits to perform the requested 
> string comparison.  See my earlier reply for the query.  So long as phone 
> numbers are unambiguous, only one row should match.
>
I really didn't look at your solution or what assumptions it was making so I 
have no comment on it. I was simply trying to share a little life experience 
with the original poster. Naturally, he's free to ignore it if he doesn't 
think it is applicable for his situation. As are you :-)

Basically, this situation just epitomizes the old "garbage in, garbage out" 
phenomenon: the data you pull out of the database can't be any better than 
the data you put into it. In this case, I doubt that the data in the 
database is particularly accurate on many of the rows so parsing it isn't 
going to magically make the data better.

The real solution is to use edits to make sure that you get good data in the 
first place; while you're at it, splitting the data into the fields you are 
likely to need later makes a lot of sense. I know that we don't always have 
the ability to control the quality of the data that we put into a table so 
your solution is probably the best that the original poster can do under the 
circumstances. But I suspect that a lot of people will get telemarketing 
calls from his organization and that a lot of those calls will be wrong 
numbers because the original numbers weren't correct or complete in the 
first place. That can be pretty distressing to the victim of the call if 
he/she sleeps at unusual hours, for example.

Anyway, I just wanted to share these observations with the original poster - 
and anyone else on the list who is following this issue - to help put some 
perspective on the matter. Again, feel free to ignore me if you like. :-)

Rhino



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/175 - Release Date: 18/11/2005

Thread
Mysql Finding the country name from country prefixabhishek jain19 Nov
  • Re: Mysql Finding the country name from country prefixRhino19 Nov
    • Re: Mysql Finding the country name from country prefixMichael Stassen19 Nov
    • Re: Mysql Finding the country name from country prefixBjörn Persson19 Nov
  • Re: Mysql Finding the country name from country prefixPeter Brawley19 Nov
    • Re: Mysql Finding the country name from country prefixMichael Stassen19 Nov
  • Re: Mysql Finding the country name from country prefixMichael Stassen19 Nov
  • Re: Mysql Finding the country name from country prefixRhino19 Nov
    • Re: Mysql Finding the country name from country prefixMichael Stassen19 Nov
  • Re: Mysql Finding the country name from country prefixRhino19 Nov
    • Re: Mysql Finding the country name from country prefixMichael Stassen20 Nov
      • Re: Mysql Finding the country name from country prefixJasper Bryant-Greene20 Nov
        • Re: Mysql Finding the country name from country prefixMichael Stassen20 Nov
        • Re: Mysql Finding the country name from country prefixBjörn Persson20 Nov