?
Solved

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

Posted on 2003-03-18
6
Medium Priority
?
8,345 Views
Last Modified: 2013-12-12
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
Comment
Question by:Jay_D
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:Jay_D
ID: 8163246
BTW, I realize that I can use a combination of substring commands to achieve the desired display format.
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 8164190
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
 
LVL 15

Accepted Solution

by:
andrewst earned 225 total points
ID: 8165895
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!

 
LVL 14

Expert Comment

by:leoahmad
ID: 8178603
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
 
LVL 14

Expert Comment

by:leoahmad
ID: 8188531
Is the both solutions not helpful for you ?????

WBR Leo
0
 

Author Comment

by:Jay_D
ID: 8190443
 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question