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 ?
WHEN '998' Then 'Uzbekistan '
WHEN '996' Then 'Kyrgyzstan '
WHEN '995' Then 'Georgia '
ELSE 'OTHERS' END ) AS Country
A simple solution is based and the fact you can test the condition inside the when in a ordinated sequence
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