Translate

2013-04-15

How to recognize in a single SQL query the area code of a phone number


Put a table of codes:

Area| Code
-------------------
Area0 | 2
Area1 | 21
Area2 | 212
Area3 | 213
Area4 | 3
Area5 | 321

A calls table

CallId | CallerId
-------------------------------
1 | 276543
2 | 214525
3 | 213987
4 | 365145
5 | 321458

Is it possible in a single query to find the area corresponding to the code? The difficulty here is obviously that the code has no fixed length, and its length is not limited.

CallId | CallerId | Area
---------------------------------------
1 | 276543 | Area0
2 | 214525 | Area1
3 | 213987 | Area3
4 | 365145 | Area4
5 | 321458 | Area5


The solution is as follow!
The trick is a subquery that selects all areas that can match a code but keeps only the longest, so the most appropriate!

SELECT CallId, CallerId,
(
SELECT Area
FROM codes
WHERE code = LEFT (CallerId, length (code))
ORDER BY length (code) DESC
LIMIT 1
)
FROM Calls


Hoping that it can be useful to someone, if someone knows a better way, however, I'm interested.

No comments: