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 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
All good points. As you say, if the numbers are user entered, they almost
certainly contain garbage. On the other hand, he could be processing logs of
actual calls, in which case the numbers would almost certainly be complete.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
