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: 
This is a pain to do efficiently. Assuming that the city codes do not have prefixes of each other (like "123" and "1234"), you can try:

```
select d.*, c.city_name
from dialedin d join
     cities c
     on c.citycode = left(d.num, length(citycode))

```

This might not optimize well, because the `left()` has columns from both tables, and that generally precludes the use of an index. An alternative is multiple joins:

```
select d.*, coalesce(c3.city_name, c4.city_name, c5.city_name)
from dialedin d left join
     cities c3
     on c3.citycode = left(d.num, 3) left join
     cities c4
     on c4.citycode = left(d.num, 4) left join
     cities c5
     on c5.citycode = left(d.num, 5) 

```

Another advantage to the multiple `join` approach is that you can take the longest prefix that matches:

```
select d.*, coalesce(c3.city_name, c4.city_name, c5.city_name)
from dialedin d left join
     cities c5
     on c3.citycode = left(d.num, 5) left join
     cities c4
     on c4.citycode = left(d.num, 4) and c5.citycode is null left join
     cities c3
     on c5.citycode = left(d.num, 3) and c4.citycode is null

```
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.