Mysql CASE with multiple substring legths

Question

I have hundreds of phone number of the world. Each has its country prefix (the prefix varies: some are 1, 2, 3 or 4 digit long) + the phone number. I want to write a mysql query, which will show me the Country name by using the prefix.

Example : If i use sub-string for the first 3 digits, its working fine. But how i can show the prefixes which are 2 or 4 digit long ?

SELECT(
CASE (SUBSTR(Number,1,3))
WHEN '998' Then 'Uzbekistan '
WHEN '996' Then 'Kyrgyzstan '
WHEN '995' Then 'Georgia '
.....
....
ELSE 'OTHERS' END ) AS Country

post some data example for better clarity

Rahul
– Rahul

2016-09-22 18:10:58 +00:00
Commented Sep 22, 2016 at 18:10 — Rahul
– Rahul, Commented Sep 22, 2016 at 18:10
It might be helpful if country code is its own field

zedfoxus
– zedfoxus

2016-09-22 18:13:04 +00:00
Commented Sep 22, 2016 at 18:13 — zedfoxus
– zedfoxus, Commented Sep 22, 2016 at 18:13

Scarabee · Accepted Answer · 2016-09-22 18:46:33Z

4

A simple solution is based on the fact that the CASE statement is evaluated sequentially.

SELECT(
    CASE 
      WHEN SUBSTR(Number,1,2) = '23' Then 'Try For 23 '   
      WHEN SUBSTR(Number,1,3) = '998' Then 'Uzbekistan '
      WHEN SUBSTR(Number,1,3) = '996' Then 'Kyrgyzstan '
      WHEN SUBSTR(Number,1,3) = '995' Then 'Georgia '
     .....
     ....
     ELSE 'OTHERS' END ) AS Country

edited Sep 22, 2016 at 18:46

Scarabee

5,7545 gold badges32 silver badges59 bronze badges

answered Sep 22, 2016 at 18:12

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ayaz Over a year ago

Is their any way that i can combine the prefixes which has 3 digits. Something like ---- WHEN SUBSTR(Number,1,3) = '998' Then 'Uzbekistan ' OR '996' Then 'Kyrgyzstan ' OR '995' Then 'Georgia '

ScaisEdge Over a year ago

@Ayaz No using OR like in you comment is not possibile .. could be using nested case when but seems too complex ..(at least for me)

Scarabee Over a year ago

You can use nested CASE statements: CASE SUBSTR(Number,1,2) WHEN '33' THEN 'France' WHEN '39' THEN 'Italy' ELSE (CASE SUBSTR(Number,1,3) WHEN '998' THEN 'Uzbekistan ' WHEN '996' THEN 'Kyrgyzstan' END) END

Collectives™ on Stack Overflow

Mysql CASE with multiple substring legths

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related