CODEWITHSUNDEEP
mysqlsql
Siavosh
Siavosh

Reputation: 2354

MySQL query to get codes of countries

I am developing an application, in one part of it I get a string as the phone number and I have to return the phone provider of that number, in database I have table prefix with this structure: 

id , name, code

Some data in it is like:

(23242, 'UK-Mobile-T Mobile', '447984'),
(23243, 'UK-Mobile-T Mobile', '447985'),
(23244, 'UK-Mobile-T Mobile', '447986'),
(23245, 'UK-Mobile-T Mobile', '447987'),
(23246, 'UK-Mobile-Vodafone', '447407'),
(23247, 'UK-Mobile-Vodafone', '447423'),

name is the provider and code is the prefix belongs to that provider and what I get as input is a phone number just like 447243xxxxx

Question is this: how should I create a query to return the UK-Mobile-Vodafone as a result when the above input is given ?

please remember length of this code is not same for every country

Upvotes: 0

Views: 1619

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

This may work for you:

select t.*
from table t
where '447243xxxxx' like concat(t.code, '%');

This assumes, among other things, that only one prefix matches each number. Otherwise you need to choose among them.

If you need to choose among them, typically you would want the longest matching one:

select t.*
from table t
where '447243xxxxx' like concat(t.code, '%')
order by length(t.code) desc
limit 1;

And then, if you want to be able to use an index, you don't want to use concat() on the code. Instead, extract the first n characters. This is easy if all have the same length (6) as in your example:

select t.*
from table t
where left('447243xxxxx', 6) = t.code;

Upvotes: 1

tjati
tjati

Reputation: 6079

Just use the LIKE-operator with GROUP BY:

SELECT NAME FROM TBL_TABLENAME WHERE CODE LIKE '447243%' group by NAME;

Upvotes: 0

Related Questions