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.
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.
Rhino
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
