Mysql – How to format a phone number in a SELECT statement

MySQLnumber formatting

I have a database with table name student.

I would like to display the register_number and phone_number of the student.

The phone_number should be in the following format:

+91-123-456-7890

and if the phone_number is NULL, then it should display N/A.

The table looks like:

R_NO      |     STUDENT_NAME    |  PHONE_NUMBER
-------------------------------------------------------
  1       |      Rajesh         |  9632545123
  2       |      Sridevi        |  9512647359
  3       |      Shiva          |  9632155862
  4       |      HariHaran      |  8426911231
  5       |      Ravi           |  9111558899
  6       |      Pauline        |  NULL

Best Answer

Different areas have differing number of digits in their prefix (wikipedia's list):

COALESCE(CONCAT('+',
  CASE
    WHEN LEFT(phone_number, 1) IN ('1', '7')
      THEN INSERT(INSERT(INSERT(phone_number, 2, 0, '-'), 6, 0, '-'), 10, 0, '-')
    WHEN CONVERT(LEFT(phone_number, 2), SIGNED) IN (21, 22, 23, 24, 25, 26, 29, 35, 37, 38, 42, 50, 59,
                                                    67, 68, 69, 80, 85, 87, 88, 96, 97, 99)
      THEN INSERT(INSERT(INSERT(phone_number, 4, 0, '-'), 8, 0, '-'), 12, 0, '-')
    ELSE   INSERT(INSERT(INSERT(phone_number, 3, 0, '-'), 7, 0, '-'), 11, 0, '-') END), 'N/A')

With the query above, all phone numbers will show as +Country_code-XXX-XXX-XXXX although each area has specific formatting rules, which vary greatly, so that formatting will look funny for certain regions, such as Australia where formatting is +54-X-XXXX-XXXX.