create or replace function format_phone
return varchar2
is 
  v_phone_number VARCHAR2(50) := '123.456.7890';
  v_format_phone_no VARCHAR2(50);
begin

  v_format_phone_no := '(' || v_phone_number;
  
  v_format_phone_no := replace(v_format_phone_no, '.', ') ');
  
  v_format_phone_no := substr(v_format_phone_no, 1, 9) ||
                              '-' ||
                              substr(v_format_phone_no, 12);
  
  return v_format_phone_no;

END;
/

create or replace function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is 
  v_phone employees.phone_number%type;
begin

  /*
    If   the length of the string is 12
    and  the 4th character (one based) is a period
    and  the 8th character (one based) is a period,
    then it's a U.S. format phone number.
  */
  IF length(p_phone_number)        = 12
  and instr(p_phone_number,'.')    = 4
  and instr(p_phone_number,'.', 5) = 8
  THEN
    v_phone := '(' || p_phone_number;
    v_phone := replace(v_phone, '.', ') ');
    v_phone := substr(v_phone, 1, 9) || '-' || substr(v_phone, 12);
    return v_phone;
  ELSE
    return p_phone_number;
  END IF;
END;
/

-- Here's a quick way to test that the function works as
-- expected:
select phone_number, format_phone(phone_number)
from employees;

create or replace function format_phone
(p_phone_number in employees.phone_number%type
)
return varchar2
is
begin

  IF regexp_instr(p_phone_number, '(\d{3})\.(\d{3})\.(\d{4})') = 1
  THEN
    return regexp_replace(p_phone_number, 
                          '(\d{3})\.(\d{3})\.(\d{4})', 
                          '(\1) \2-\3');
  ELSE
    return p_phone_number;
  END IF;

END;
/
