CODEWITHSUNDEEP
sqlsql-serversql-function
AndroidAL
AndroidAL

Reputation: 1119

SQL Function to assign areacode to phone number

Is it possible to create a function that assigns a phone areacode(+44) to a mobile number depending on the value of the country field.

Upvotes: 0

Views: 3962

Answers (2)

Deep Kalra
Deep Kalra

Reputation: 1428

You can steal a list of country codes/country abbr from here https://countrycode.org/ and assign country id's to them using Identity column.

Create a new table using this data. Something that looks like:

Country | Country_Code | Country_ID

Then, I suspect, you have a table with something like:

 Something | Phone_Number | Country | Country_ID

Make changes to your existing table to add Country_ID, to improve performance:

ALTER TABLE existing_table 
ADD country_id int;

UPDATE e 
SET    e. country id = n.country_id 
FROM   existing_table e 
JOIN   new_table n 
ON     e.country = n.country

You now just need some SQL to put it together (not a function):

SELECT e.something, 
       FORMAT(CONCAT(n.country_code,' ', e.phone_number), '###-##-####') AS CompletePhone
FROM   existing_table e
       JOIN new_table n
         ON e.country_id = n.country_id

Upvotes: 1

JNevill
JNevill

Reputation: 50219

You can steal a list of country codes/country abbr from here https://countrycode.org/

Create a new table using this data. Something that looks like:

Country | Country_Code

Then, I suspect, you have a table with something like:

 Something | Phone_Number | Country

You now just need some SQL to put it together (not a function):

SELECT existing_table.something, new_table.Country_code + " " + existing_table.phone_number
FROM existing_table
    JOIN new_table ON existing_table.country = new_table.country

And Bob's your uncle.

Upvotes: 1

Related Questions