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: 
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))

```
As solution mentioned by Paul in comments, you an also modify it as

```
        JOIN ON city_code = LEFT(num, 5) or city_code = LEFT(num, 3)

```

**EDIT**

Lets have a look at possible scenarios. If you have max 5 digit code and also prefixed by a number 7 or 8, then total of 6 digits would be there. Your generalized `join` clause will be

```
       JOIN ON LEFT(num, 6) LIKE '%'+city_code+'%'

```

Hope this might work.