How to match city code in telephone number with Informix SQL?

Question

I have a couple of tables. One contains dialed in phone numbers:

  num
  84951234567
  74957654321
  4951357246
  83855112345
  73855154321
  3855113524

Another has city codes:

  city_code      city_name
  495            Moscow
  38551          Kalmanka

I need to get the following:

  num             call_from
  84951234567     Moscow
  74957654321     Moscow
  4951357246      Moscow
  83855112345     Kalmanka
  73855154321     Kalmanka
  3855113524      Kalmanka

Phone number with city code always have 10 digits; it can be preceded with 7 or 8 or nothing. City code can have from 3 to 5 digits. Num is stored as VARCHAR. Is this possible to solve this with SQL?

Answer 1

As said in another answer, is complicated to make this query efficient. With this query you obtain what you want:

SELECT p.num, c.city_name
  FROM phones p, cities c
 WHERE LEFT(p.num, LENGTH(c.city_code)+1) MATCHES "*" || c.city_code || "*"

Because can be a prefix before the city_code in the phone number I take a substring of length(city_code) + 1

* Edit *

I make the join with outer to select phones without city_code, and because the phone with city code is always 10 digits, first I substring the last 10 digits of phone (as shown in another answer)

SELECT p.num, c.city_name
  FROM phones p, OUTER cities c
 WHERE c.city_code = LEFT(RIGHT(p.num, 10), LENGTH(c.city_code))