Jay_D
asked on
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.
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.
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)
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
create or replace procedure phone ( v_phone in out varchar2)
is
begin
v_phone :='('||substr(v_phone,1,3)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)||'-'||sub
FORMATED
------------------------
(123) 456-7890
WBR Leo
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)||'-'||sub
FORMATED
------------------------
(123) 456-7890
WBR Leo
Is the both solutions not helpful for you ?????
WBR Leo
WBR Leo
ASKER
I think the type of solution I was looking for just doesn't exist. But I appreciate the feedback from both experts.
ASKER