How to format a phone number in a SELECT statement?

Question

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

Answer 1

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.

Answer 2

One part could be handled thus:

IFNULL(phone_number, 'N/A');

But, really, your formatting requirement is best done in your application code, not SQL.

Answer 3

The below link has code for a mask function that works like this:

SELECT mask(991234567890, '+##-###-###-####')
-> +99-123-456-7890

https://stackoverflow.com/questions/10112718/mysql-output-masking-i-e-phone-number-ssn-etc-display-formatting

As for N/A, something like this should work:

SELECT IfNull(phone_number, 'N/A')

Answer 4

As french citizen, we have different way to write this.

Here is how I show the phone numbers :

SELECT
    UserName,
    CASE
        WHEN Mobile IS NULL OR Mobile = '' THEN ''
        ELSE CONCAT(SUBSTR(Mobile,1,2), ' ', SUBSTR(Mobile,3,2), ' ', SUBSTR(Mobile,5,2), ' ', SUBSTR(Mobile,7,2), ' ', SUBSTR(Mobile,9,2))
    END as 'Mobile',
FROM Users

The result will be like this 01 23 45 67 89:

UserName	Mobile
Some name	01 23 45 67 89
OtherGuy	06 54 12 11 42

Answer 5

 select concat('(',substr(phonenumber,1,3),')' ,substr(phonenumber,4,3),'-', substr(phonenumber,7,4))

Answer 6

select cname
      ,coalesce(concat('+91-',substring(contactNo,1,3),'-',substring(contactNo,4,3),'-',substring(contactNo,7)),'N/A') as Contact_Number
  from company
 order by cname asc;