phone number format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

phone number format

Author

Topic

akas
Starting Member

42 Posts

Posted - 2008-08-22 : 12:30:42

hi guys,

i have phonenumber field in my query and the results display for all phones are like:

715.355.206200
80422538640000
80486476830000
80469202040000

i want in this format - (715)355-2062

so can anyone help me to getting this?

Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-22 : 12:57:40

This will work for the sample data you gave
declare @phone varchar(20)
set @phone = '715.355.206200'
set @phone = REPLACE(@phone ,'.','')

select '(' + SUBSTRING(REPLACE(@phone ,'.','') ,1,3) + ')'+SUBSTRING(REPLACE(@phone ,'.','') ,4,3)+'-'+SUBSTRING(REPLACE(@phone ,'.',''),8,4)

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 13:38:59

will be having only these patterns appearing in phone number field?

akas
Starting Member

42 Posts

Posted - 2008-08-22 : 14:43:20

thanks a lot!!!

i did like this

select '(' + SUBSTRING(phnumbr1, 1, 3) + ') ' +
SUBSTRING(phnumbr1, 4, 3) + '-' +
SUBSTRING(phnumbr1, 7, 4) from phontab

and it worked properly

thanks a lot jim and visakh.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-22 : 15:19:04

that will turn 715.355.206200
into
(715) .35-5.20

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources