OraFAQ Forum: SQL & PL/SQL » Mask for Telephone Number

Home » SQL & PL/SQL » SQL & PL/SQL » Mask for Telephone Number

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Mask for Telephone Number [message #39140]

Wed, 19 June 2002 22:02

Arjan Sadhwani
Messages: 3
Registered: April 2002

Junior Member

Hello: How I do create a mask for telephone number to display as(123)345-3456. d_phone is stored as varchar2 (11) in customer table;
Thanks for help
Arjan

Report message to a moderator

Re: Mask for Telephone Number [message #39142 is a reply to message #39140]

Thu, 20 June 2002 06:35

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> ed
Wrote file afiedt.buf

  1* select '('||substr(123456789,1,3)||')'||substr(123456789,4,11) from dual
SQL> /

'('||SUBSTR
-----------
(123)456789

SQL> select * from test;

PH
-----------
123345-3456

SQL> select '('||substr(ph,1,3)||')'||substr(ph,4,11) from  test;

'('||SUBSTR(P
-------------
(123)345-3456

Report message to a moderator

Re: Mask for Telephone Number [message #39162 is a reply to message #39140]

Sat, 22 June 2002 10:02

Arjan Sadhwani
Messages: 3
Registered: April 2002

Junior Member

Hello: Thanks for your reply. Is there any way I can use the built in FM function for converting. The text book gives FM"("999") "999"-"999 as the fucntion
Your help is appreciated
Arjan

Report message to a moderator

Re: Mask for Telephone Number [message #39164 is a reply to message #39140]

Sat, 22 June 2002 14:25

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I have no idea about any in-built function name FM in oracle.
It may be something dealing with oracle applications etc.
any how, you can just create a function if you want. something like this~

SQL> desc test;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(100)

SQL> select * from test;

ID
----------------------------------------------------------------------------------------------------
123345-3456

SQL> get my_FM
  1  create or replace function  my_fm
  2  (v_in in varchar2)
  3  return varchar2 is retval varchar2(100);
  4  begin
  5  retval:=substr(v_in,1,3)||')'||substr(v_in,4,11);
  6  return retval;
  7* end;
SQL> /

Function created.

SQL> select my_fm(id) from test;

MY_FM(ID)
----------------------------------------------------------------------------------------------------
123)345-3456

SQL>

Report message to a moderator

Previous Topic:	problem with creating trigger
Next Topic:	insert to user_tab_comment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 06:11:24 CDT 2024