Re: formating

CREATE FUNCTION format_telno
(as_telno IN VARCHAR2

)RETURN VARCHAR2
IS
BEGIN
  RETURN '(' || SUBSTR(as_telno,1,3 ) || ')' || SUBSTR(as_telno,4,3 ) || '-' || SUBSTR(as_telno,7);
END; select format_telno(mytelnocol) from mytable;

"Bastiaan Schaap" <fake_address_at_nomail.com> wrote in message news:UTgY6.1$776.274_at_psinet-eu-nl...
You cannot do it with a format mask I'm afraid..... I tried some things with translate but I don't use it often, so here's my solution or two cents, whatever you want to call it:

SQL> create table a( a varchar2(10) );

Table created

SQL> insert into a( a ) values ( '1234567890' );

1 row inserted

SQL> commit;

Commit complete

SQL> select '(' || substr( a.a, 1, 3 ) || ')' || substr( a.a, 4, 3 ) || '-' || substr( a.a, 7 ) AS tel_no from a a;

TEL_NO

SQL> This will work, as long as you have the same type of numbers in your column....

HTH,

--
Bastiaan Schaap
________________________________
"Dear God! What's on the end of that leash?" "I tamper with nature as a
hobby."