how can formate a phone number for all countreis in pl

CREATE OR REPLACE PROCEDURE format_phone
(phone_no IN OUT VARCHAR2) IS
BEGIN
phone_no := '(' || SUBSTR(phone_no,1,3) ||
')' || SUBSTR(phone_no,4,3) ||
'-' || SUBSTR(phone_no,7);
END format_phone;

this code just for one country I want to formate a phone number for all countries how can I do it by this code please

CodePudding user response：

Country-by-country, I'm afraid. Procedure should then contain yet another parameter - country - which would then be used to properly format phone number.

Though, why procedure, why not function? They are designed for such a purpose and can be called directly from SQL, in SELECT statements. Procedures, on the other hand, require PL/SQL.

Something like this:

create or replace function format_phone (par_phone in varchar2, par_country in varchar2)
  return varchar2
is
begin
  return case when par_country = 'HR' then 
                   substr(par_phone, 1, 3) || ...
              when par_country = 'USA' then 
                   substr(par_phone ...)
              when par_country = 'UK' then ...
         end;
end;

CodePudding user response：

In fact there is an international standard for phone numbers. It is

If you would like to use it you'll need countries phone codes which you can find here.