changing telephone format [message #674376] |
Thu, 24 January 2019 00:48 |
|
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 #674382 is a reply to message #674381] |
Thu, 24 January 2019 01:38 |
|
Michel Cadot
Messages: 68653 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.
|
|
|
|