Search code examples
sqlphpmyadminformatsubstringvtiger

Format phone number to international

I already found "How to format a numeric column as phone number in SQL" but it just destroyed my number.

I have a phone number like 064184335 and want to format it to +49 641 84335.

I tried:

UPDATE vtiger_contactdetails
    SET phone = '+49' +' '+ SUBSTRING(phone, 2, 2 ) + ' ' + SUBSTRING(phone, 4, 3)

but I got 295 as phonenumber. Also the 3 in the second SUBSTRING should be the rest of the phone number.

If I want to change every phone number in different tables shouldn't it work with:

UPDATE vtiger_account, vtiger_contactdetails, vtiger_contactsubdetails
SET vtiger_account.phone = CONCAT('+49', ' ', SUBSTRING(vtiger_account.phone, 2, 3 ), ' ', SUBSTRING(vtiger_account.phone, 5, length(vtiger_account.phone)))
SET vtiger_account.otherphone = CONCAT('+49', ' ', SUBSTRING(vtiger_account.otherphone, 2, 3 ), ' ', SUBSTRING(vtiger_account.otherphone, 5, length(vtiger_account.otherphone)))
SET vtiger_contactdetails.phone = CONCAT('+49', ' ', SUBSTRING(vtiger_contactdetails.phone, 2, 3 ), ' ', SUBSTRING(vtiger_contactdetails.phone, 5, length(vtiger_contactdetails.phone)))
SET vtiger_contactdetails.mobile = CONCAT('+49', ' ', SUBSTRING(vtiger_contactdetails.mobile, 2, 3 ), ' ', SUBSTRING(vtiger_contactdetails.mobile, 5, length(vtiger_contactdetails.mobile)))
SET vtiger_contactsubdetails.homephone = CONCAT('+49', ' ', SUBSTRING(vtiger_contactsubdetails.homephone, 2, 3 ), ' ', SUBSTRING(vtiger_contactsubdetails.homephone, 5, length(vtiger_contactsubdetails.homephone)))
SET vtiger_contactsubdetails.otherphone = CONCAT('+49', ' ', SUBSTRING(vtiger_contactsubdetails.otherphone, 2, 3 ), ' ', SUBSTRING(vtiger_contactsubdetails.otherphone, 5, length(vtiger_contactsubdetails.otherphone)))
SET vtiger_contactsubdetails.assistantphone = CONCAT('+49', ' ', SUBSTRING( vtiger_contactsubdetails.assistantphone, 2, 3 ), ' ', SUBSTRING( vtiger_contactsubdetails.assistantphone, 5, length( vtiger_contactsubdetails.assistantphone)))

How do I ignore already formatted numbers?

Solution

  • I'm guessing that you are using MySQL. The way to concatenate strings is concat():

    UPDATE vtiger_contactdetails
    SET phone = CONCAT('+49', ' ', SUBSTRING(phone, 2, 2 ), ' ', SUBSTRING(phone, 4, 3));

    To get all the characters, just use two arguments to SUBSTRING():

    UPDATE vtiger_contactdetails
    SET phone = CONCAT('+49', ' ', SUBSTRING(phone, 2, 2 ), ' ', SUBSTRING(phone, 4));

    Note that this works in MySQL, but not in all databases.