| phone format [message #373522] |
Fri, 20 April 2001 11:31  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
i am trying to write a function for formatting phone numbers based on the input string and a specific format
for example to_char('1234567890','999-999-9999')
i want to
to_char('1234567890','(999)999-9999')
but the only delimiters it takes are , and $ how can i include () and - inside the format
|
|
|
|
| Re: phone format [message #373524 is a reply to message #373522] |
Fri, 20 April 2001 13:39  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi
this function will convert a 10 digit number to a phone number of (999)999-9999 format.......
Will this help?
create or replace function to_phone(phonein number)
return varchar2 is
phoneout varchar2(13);
begin
phoneout := '(' ||substr(phonein, 1, 3) || ')' || substr(phonein, 4, 3) || '-' || substr(phonein, 7);
return phoneout;
end;
/
SQL> select to_phone(1234567890) from dual;
TO_PHONE(1234567890)
----------------------
(123)456-7890
Bala.
|
|
|
|
| Re: phone format [message #373525 is a reply to message #373524] |
Fri, 20 April 2001 14:33 |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
thanks baala
that will work for USA phone format.. but i am trying to write a general function. that can take any 10 digit phone no and supply a format for it ..for example Canada format would be just
999-999-9999... and other country will be different... is it possible to write a fn which will
fn('10digit phone number','format')
returns appropriate formatted phone number
thanks in advance
|
|
|
|
| Re: phone format [message #373529 is a reply to message #373525] |
Fri, 20 April 2001 20:52 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Try this:
select TO_CHAR(1234567890, 'L999G999C9999', 'NLS_NUMERIC_CHARACTERS = '' )'' NLS_CURRENCY = ''('' NLS_ISO_CURRENCY = ''-''') "Char" from dual;
I have not try the above statement to make sure it works, however I know the following statement will include the {}.
select TO_CHAR(1234567890, 'L999G9999999', 'NLS_NUMERIC_CHARACTERS = '' )'' NLS_CURRENCY = ''('' ') "Char" from dual;
Char
-------------
{123)4567890
Element Description Initialization Parameter
------- ----------- ------------------------
D Decimal character NLS_NUMERIC_CHARACTERS
G Group separator NLS_NUMERIC_CHARACTERS
C ISO currency symbol NLS_ISO_CURRENCY
L Local currency symbol NLS_CURRENCY
For more information:
http://info-it.umsystem.edu/oracle/svslr/svslr.2.0172.html
HTH
--Cindy
|
|
|
|
| Re: phone format [message #373538 is a reply to message #373525] |
Sun, 22 April 2001 22:23 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
this func will convert from any format to any format.
create or replace function to_phone(phonein number, p_format varchar2)
return varchar2 is
phoneout varchar2(20) default '';
vlen number;
cnt pls_integer default 0;
cnt2 pls_integer default 0;
begin
vlen := length(p_format);
for i in 1..vlen loop
cnt := cnt + 1;
if substr(p_format, cnt, 1) <> '9' then
phoneout := phoneout || substr(p_format, cnt, 1);
cnt2 := cnt2 + 1;
else phoneout := phoneout || substr(phonein, (cnt - cnt2), 1);
end if;
end loop;
return phoneout;
end;
/
SQL> select to_phone(1234567890, '(999)-999-9999') from dual;
TO_PHONE(1234567890,'(999)-999-9999')
---------------------------------------------------------------
(123)-456-7890
SQL> select to_phone(12345678, '999-99999') from dual
TO_PHONE(12345678,'999-99999')
--------------------------------------------------------
123-45678
Bala.
|
|
|
|
| Re: phone format [message #373539 is a reply to message #373525] |
Sun, 22 April 2001 22:53 |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
thanks a ton bala for youe help and thanks cindy..all your answers were really helpful guys.
thanks again
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
