• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9577
  • Last Modified:

How do I apply a format mask to, say, a phone number?

I have a VARCHAR2 field named s_phone, and it stores the values using ten digits, with no other characters, as in 3136541234.  I want to display that phone number as (313)654-1234.  I have indications (from a textbook) that I can use FM"("999")"999"-"9999 somehow, but I can't seem to apply this correctly.  For example, I have tried variations like these below to no avail:

SELECT s_phone FM"("999")"999"-"9999
FROM student;

SELECT TO_CHAR (s_phone, 'FM(999)999-9999')
FROM student;

Thanks,
Jay Davis

P.S.  I'm trying this service for the first time, and now I just saw that I only have 75 points.  Well, I'll just have to submit the question and hope for kindness.
0
Jay_D
Asked:
Jay_D
  • 3
  • 2
1 Solution
 
Jay_DAuthor Commented:
BTW, I realize that I can use a combination of substring commands to achieve the desired display format.
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
You should create a procedure like this:

create or replace procedure phone ( v_phone in out varchar2)
is
begin
v_phone :='('||substr(v_phone,1,3)||')'||substr(v_phone,4,3)||'-'||substr(v_phone,7);
end;
/

Then use this procedure as:

SQL> var fone varchar2(30)
SQL> begin
  2  :fone:='123456789';
  3  end;
  4  /

PL/SQL procedure successfully completed.
SQL> execute phone(:fone)

PL/SQL procedure successfully completed.
SQL> print fone

FONE
--------------------------------
(123)456-789


WBR, Leo
0
 
andrewstCommented:
I believe that formats like FM"("999")"999"-"9999 are valid in Oracle Forms as Item Format Masks, but they are not valid for the SQL TO_CHAR function.  The full set of numeric formats is described here:

http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements4a.htm#34773

You could of course create your own function to do the conversion like:

select format_phone( '3136541234' ) fp from dual;

fp
-------------
(313)654-1234
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Muhammad Ahmad ImranDatabase DeveloperCommented:
If you would like just a select statement then use this one:

SQL> create table temp (a varchar2(20));

Table created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select '('||substr(a,1,3)||') '||substr(a,4,3)||'-'||substr(a,7) formated from temp;

FORMATED
------------------------
(123) 456-7890

WBR Leo
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
Is the both solutions not helpful for you ?????

WBR Leo
0
 
Jay_DAuthor Commented:
 I think the type of solution I was looking for just doesn't exist.  But I appreciate the feedback from both experts.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now