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