SQL help needed.. If I have a MySQL table with dialing codes and a corresponding description (see below) and I want to lookup the best match for a phone number.. What would the SQL look like to do it? or would it take more than just SQL to get to the best result? Thanks.. Later.. Example numbers, (random end digits so I don't know who's they are.) 00442085673456 - UK London 00441273786524 - UK Geographic 00441444456789 - UK Geographic 00447796345678 - Vodafone Mobile 00447930987321 - O2 Mobile 0027117654321 - South Africa 0016047863252 - USA/Canada ______________________ Code | Data ----------------------------------- 001 USA/Canada 0027 South Africa 0031 Netherlands 0032 Belguim 0033 France 0034 Spain 0039 Italy 0041 Switzerland 0043 Austria 0045 Denmark 0046 Sweden 0047 Norway 0049 Germany 00441 UK geographic 00442 UK geographic 0044207 UK London 0044208 UK London 0044370 Vodafone 0044378 Vodafone 0044390 Vodafone 0044401 O2 0044402 O2 0044403 O2 0044408 O2 0044410 O2 0044411 O2 0044421 Vodafone 0044467 Vodafone 0044468 Vodafone 0044585 O2 0044589 O2 0044770 O2 0044771 O2 0044775 O2 0044779 Vodafone 0044783 Vodafone 0044793 T-Mobile 0044794 T-Mobile 0044795 T-Mobile 0044797 Orange 0044799 Vodafone 0044080 Free call 0044802 O2 0044831 Vodafone 0044836 Vodafone 0044845 Local Rate 0044850 O2 0044860 O2 0044870 National rate 0044966 Orange 0044973 Orange 0044976 Orange 0044990 UK geographic
On Mon, 10 Nov 2003 at 09:56, WipeOut wrote:> If I have a MySQL table with dialing codes and a corresponding > description (see below) and I want to lookup the best match for a > phone number.. What would the SQL look like to do it?I don't know MySQL very well, but maybe you also get an idea of how it can be done if I describe it in terms of PostgreSQL: SELECT DISTINCT *,length(code) FROM a WHERE '00442085673456' LIKE (code || '%') ORDER length(code) DESC; Now, the first row in the result set contains the longest matching prefix of the given number that exists in the table, if there is any.> or would it take more than just SQL to get to the best result?If MySQL doesn't support using a column as a pattern for the LIKE operator you might have to make multiple queries and allways strip one more digit from the end of the number until you find a match. cu Reinhard
The cleanest way to do this would be to switch to PostgreSQL and then define a "phone_number" type and a "prefix_match" operator maybe calling it <>then you could use SQL like this: Select data where Code <>= Number_dialed. That's the best thing about Postgresql and other object/relational DBMSes: User defined objects. --- WipeOut <wipe_out@onetel.com> wrote:> SQL help needed.. > > If I have a MySQL table with dialing codes and a corresponding > description (see below) and I want to lookup the best match for a > phone > number.. What would the SQL look like to do it? or would it take more > > than just SQL to get to the best result? > > Thanks.. > > Later.. > > Example numbers, (random end digits so I don't know who's they are.) > > 00442085673456 - UK London > 00441273786524 - UK Geographic > 00441444456789 - UK Geographic > 00447796345678 - Vodafone Mobile > 00447930987321 - O2 Mobile > 0027117654321 - South Africa > 0016047863252 - USA/Canada > ______________________ > Code | Data > ----------------------------------- > 001 USA/Canada > 0027 South Africa > 0031 Netherlands > 0032 Belguim > 0033 France > 0034 Spain > 0039 Italy > 0041 Switzerland > 0043 Austria > 0045 Denmark > 0046 Sweden > 0047 Norway > 0049 Germany > 00441 UK geographic > 00442 UK geographic > 0044207 UK London > 0044208 UK London > 0044370 Vodafone > 0044378 Vodafone > 0044390 Vodafone > 0044401 O2 > 0044402 O2 > 0044403 O2 > 0044408 O2 > 0044410 O2 > 0044411 O2 > 0044421 Vodafone > 0044467 Vodafone > 0044468 Vodafone > 0044585 O2 > 0044589 O2 > 0044770 O2 > 0044771 O2 > 0044775 O2 > 0044779 Vodafone > 0044783 Vodafone > 0044793 T-Mobile > 0044794 T-Mobile > 0044795 T-Mobile > 0044797 Orange > 0044799 Vodafone > 0044080 Free call > 0044802 O2 > 0044831 Vodafone > 0044836 Vodafone > 0044845 Local Rate > 0044850 O2 > 0044860 O2 > 0044870 National rate > 0044966 Orange > 0044973 Orange > 0044976 Orange > 0044990 UK geographic > > _______________________________________________ > Asterisk-Users mailing list > Asterisk-Users@lists.digium.com > http://lists.digium.com/mailman/listinfo/asterisk-users====Chris Albertson Home: 310-376-1029 chrisalbertson90278@yahoo.com Cell: 310-990-7550 Office: 310-336-5189 Christopher.J.Albertson@aero.org KG6OMK __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree
> >Thanks everyone for your help on this.. > > > >For those who are interested I have done some speed tests on these two > >queries (below) on my server and the results are.. > > > >Test script of 1000 quieries.. > >Query1 ("code" field not indexed) = 47.183s > >Query1 ("code" field indexed) = 45.731s > >Query2 ("code" field not indexed) = 109.321s > >Query2 ("code" field indexed) = 2.302sTried fulltext indexing?
take off your mail list NOW or ELSE
::take off your mail list NOW or ELSE Oooh... I have to know... or else what? Now... for the self empowered type... You can go to http://lists.digium.com and remove yourself... but I still would like to see what is meant by "or ELSE". Tom Walsh Network Administrator http://www.ala.net/