Home » SQL & PL/SQL » SQL & PL/SQL » changing telephone format (12c)
changing telephone format [message #674376] Thu, 24 January 2019 00:48 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i am trying to convert the telephone data of employees stored in table in a specific format. for example it will be like 00971528767123 the first 5 letters are for country code, instead i wanted them in a model as +971 528767123 , but the trick is if there is a land line number with area code it has be replaced in another model for example 0097444325678 to be replaced as +974 (04) 4325678 , this number will be difference as it will have 0 for state code after country code 00971.
create table ot_per_contact ( id number , contact_Val varchar2(200));
insert into ot_per_contact(id,contact_val) values ('1','00971528767123');
insert into ot_per_contact(id,contact_val) values ('1','00971568767123');
insert into ot_per_contact(id,contact_val) values ('2','00974568767123');
insert into ot_per_contact(id,contact_val) values ('3','00974044235678');

Re: changing telephone format [message #674379 is a reply to message #674376] Thu, 24 January 2019 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So what should be the result for the data you gave?

Re: changing telephone format [message #674381 is a reply to message #674379] Thu, 24 January 2019 01:32 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
desired output is


+971 528767123
+971 568767123
+974 568767123
+974 04 4235678


Re: changing telephone format [message #674382 is a reply to message #674381] Thu, 24 January 2019 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col contact_val format a15
SQL> col new_contact format a15
SQL> select id, contact_val,
  2         '+' || substr(contact_val,3,3) || ' ' ||
  3         substr(contact_val,6,2) ||
  4         decode(substr(contact_val,6,1),'0',' ') ||
  5         substr(contact_val,8) new_contact
  6  from ot_per_contact
  7  /
        ID CONTACT_VAL     NEW_CONTACT
---------- --------------- ---------------
         1 00971528767123  +971 528767123
         1 00971568767123  +971 568767123
         2 00974568767123  +974 568767123
         3 00974044235678  +974 04 4235678

4 rows selected.
Re: changing telephone format [message #674383 is a reply to message #674382] Thu, 24 January 2019 03:45 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Michael.
Previous Topic: How to simulate crash database?
Next Topic: SQL Query execute more records in WHERE CLAUSE
Goto Forum:
  


Current Time: Thu Mar 28 07:59:47 CDT 2024