Get phone number area code and lookup region on different table

Question

I have a table(customers) with a phone field (10 digits) an area_code table with area codes and regions.

Table | Field customers | phone area_code | area_code, region

I am trying to create a query that would allow me to select the area code from the phone number field and match it against the area_code table.

For example, I want to get all phone numbers in the of Alabama (AL).

I was able to use this to get the area_code from phone field but I do not know how to create the rest of the statement. Thank you.

SELECT distinct
  mid(phone, 1, 3) as area_code, 
  mid(phone, 4, 3) as prefix, 
  mid(phone, 7, 4) as phone
from customers;

Output:

+-----------+--------+-------+
| area_code | prefix | phone |
+-----------+--------+-------+
|           |        |       |
| 106       | 202    | 4921  |
| 105       | 311    | 7191  |
| 107       | 275    | 3678  |
| 111       | 112    | 1212  |
+-----------+--------+-------+

table: area_code

output:
+-----------+--------+
| area_code | region |
+-----------+--------+
|       201 | NJ     |
|       202 | DC     |
|       203 | CT     |
|       204 | MB     |
|       205 | AL     |
+-----------+--------+

Do you guarantee that the area_code is exactly 3 digits ? Do you have the input as the region name (as AL) or you have it as the code (as 205) ? — Mohammad Awwaad, Aug 27 '20 at 23:16
Given that - from your question details - the area_code's is 3 digits, And your input is the area's name (such as 'AL'), you can get all phone numbers related to the given area as: SELECT c.phone FROM customers c JOIN area_code ac ON (MID(c.phone, 1, 3) = ac.area_code) WHERE ac.region = 'AL'; — Mohammad Awwaad, Aug 27 '20 at 23:33

Get phone number area code and lookup region on different table

0 Answers0