CODEWITHSUNDEEP
mysqlsql
Les
Les

Reputation: 330

Format results of a string phone number in SQL query

I need to format the query results of phone numbers so that the phone string has a country code if it is not already in that format.

The DB (MySQL) currently contains phone numbers in two formats. Below is an example of the two ways the phone number is represented:

I want the query results to be such that the phone number is in the international format, if it is in the local number format. For example, if the number was stored as 0799123456 the query result should be +111799123456

SELECT phone 
FROM customer c, order o
WHERE c.id = o.cust_id

How do I manipulate my select statement to return the string in the international format if the phone number is in the local format? Basically, I'm trying to prepend +1117 if the number starts with 07 (so replace the 07 with +1117) in the string.

Upvotes: 0

Views: 803

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

Just use string functions:

SELECT CASE
    WHEN phone LIKE '07%' THEN INSERT(phone, 1, 1, '+111')
    ELSE phone
END AS phone_formatted
FROM (
    SELECT '0799123456' AS phone UNION
    SELECT '+111799123456'
) x

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below

select case when left(phone,2)='07' then 
concat('+1117',substring(phone,3,length(phone)-2)) else phone end as newphone
FROM customer c join `order` o WHERE c.id = o.cust_id

Upvotes: 0

Related Questions