----- Original Message -----
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Cc: "abhishek jain" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
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"
<[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Cc: "abhishek jain" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
