Skip to Main Content
  • Questions
  • How can I format a telephone number using to_char

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 16, 2004 - 1:35 pm UTC

Last updated: April 10, 2005 - 3:10 pm UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

I have telephone numbers stored as number. How can I format the output to return (xxx)-xxx-xxxx in SQL


and Tom said...

ops$tkyte@ORA9IR2> create table t ( x number );
Table created.

ops$tkyte@ORA9IR2> insert into t values ( 1231231234 );
1 row created.


a simple substr will break it out:

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, substr(x,1,3), substr(x,4,3), substr(x,7,4)
2 from t
3 /

X SUB SUB SUBS
---------- --- --- ----
1231231234 123 123 1234

a little concatentation to put it back:

ops$tkyte@ORA9IR2> select x, '('||substr(x,1,3)||')-'||substr(x,4,3)||'-'||substr(x,7,4)
2 from t
3 /

X '('||SUBSTR(X,
---------- --------------
1231231234 (123)-123-1234

However, if your numbers are not well formed:

ops$tkyte@ORA9IR2> insert into t values ( 1231234 );

1 row created.

ops$tkyte@ORA9IR2> select x, substr(x,1,3), substr(x,4,3), substr(x,7,4)
2 from t
3 /

X SUB SUB SUBS
---------- --- --- ----
1231231234 123 123 1234
1231234 123 123 4

you might need to do this:

ops$tkyte@ORA9IR2> select x,
2 substr(to_char(x,'fm0000000000'),1,3),
3 substr(to_char(x,'fm0000000000'),4,3),
4 substr(to_char(x,'fm0000000000'),7,4)
5 from t
6 /

X SUB SUB SUBS
---------- --- --- ----
1231231234 123 123 1234
1231234 000 123 1234


Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 16, 2004 - 4:39 pm UTC


Reader, December 17, 2004 - 12:32 am UTC


formatting telephone number

Potkin Nemat, December 17, 2004 - 11:34 am UTC

It may be better to format the telephone numbers into pure numbers first and strip out all the spaces, '-', '?' etc. first and then use the substr method, Tom suggests.
That way all the telephone numbers will be in a similar initial format.

The first column shows the data at random format and teh second column after they have been cleaned.

SELECT tel,translate(t.tel,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ?-','0123456789')
FROM telephone t

TEL TRANSLATE(T.TEL,'0123456789ABC
123-9989 999 1239989999
123 9989 999 1239989999
123 9989 999 1239989999
123 ?9989 999 1239989999
123 ?900-89 9 123900899

Tom Kyte
December 17, 2004 - 2:44 pm UTC

they started with "i have telephone numbers stored as number".....

so they are pure numbers.

I was concered with numbers that were not exactly 10 digits long.

formatting telephone number

Potkin Nemat, December 17, 2004 - 11:37 am UTC

sorry the text format of my previous posting got distorted after I submitted it. Let me try again, fingers crossed!

It may be better to format the telephone numbers into pure numbers first and strip out all the spaces, '-', '?' etc. first and then use the substr method, Tom suggests.
That way all the telephone numbers will be in a similar initial format.

The first column shows the data at random format and teh second column after they have been cleaned.

SELECT tel,
translate(tel,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ?','0123456789')
FROM telephone

TEL TRANSLATE(T.TEL,'0123456789ABC
123-9989 999 1239989999
123 9989 999 1239989999
123 9989 999 1239989999
123 ?9989 999 1239989999
123 ?900-89 9 123900899

A reader, December 27, 2004 - 11:42 am UTC

Try this

select replace(to_char('8374324612','999,999,9999'),',','-') from dual;



that won't do

another reader, January 04, 2005 - 11:02 am UTC

The above select won't clean out the string if it contains spaces, escape chars and other non-numeric characters. I fail to see its relevance.

Link to documentation

otn, April 10, 2005 - 2:54 pm UTC

Tom

substr(to_char(x,'fm0000000000'),1,3),


I need the link to the document which gives all the formats for strings , numbers and dates.

Please give the same.



Tom Kyte
April 10, 2005 - 3:10 pm UTC

SQL Reference Manual. All things sql including the functions to be found therein...

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#34512 <code>