List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 19 2005 6:05pm
Subject:Re: Mysql Finding the country name from country prefix
View as plain text  
abhishek jain wrote:
> 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 to match that to india.
> I cannot 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.


I believe the following query does what you describe:

   SELECT name FROM country_table
   WHERE '911122334455' LIKE CONCAT(prefix, '%');

or equivalently

   SELECT name FROM country_table
   WHERE '911122334455' REGEXP CONCAT('^', prefix);

Depending on your data, there could be more than one match.  For example, the 
string '12345551212' (a phone number in Ohio) would return USA and ZXF from your 
sample table above.  I suppose that if these are phone numbers (not my area of 
expertise), they must be arranged so that can't happen.

Michael


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