make to look like a telephone number

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

General SQL Server Forums

New to SQL Server Programming

make to look like a telephone number

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2007-08-29 : 16:49:28

I have a column that has telephone numbers that look like 90939067440000

I need them to come out looking like
(###)###-####x###
I have no idea where to begin

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-29 : 16:54:28

You need to the formatting at the front-end or presentation layer.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

gavakie
Posting Yak Master

221 Posts

Posted - 2007-08-29 : 16:56:33

im literally a week new to this an example would be great

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-08-29 : 17:30:29

Should not be doing this in SQL...

but because I'm bored...


Declare @phone varchar(20)
Set @phone = '90939067440000'
--(###)###-####x###
Select @phone, 
	coalesce(
		stuff(stuff(stuff(stuff(@phone,11,0,'x'),7,0,'-'),4,0,')'),1,0,'('),
		stuff(stuff(stuff(@phone,7,0,'-'),4,0,')'),1,0,'('))

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-29 : 17:43:29

quote:
Originally posted by Seventhnight

Should not be doing this in SQL...

but because I'm bored...

Declare @phone varchar(20)
Set @phone = '90939067440000'
--(###)###-####x###
Select @phone,
coalesce(
stuff(stuff(stuff(stuff(@phone,11,0,'x'),7,0,'-'),4,0,')'),1,0,'('),
stuff(stuff(stuff(@phone,7,0,'-'),4,0,')'),1,0,'('))

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

That's a whole lot of Stuff()

sbalaji
Starting Member

48 Posts

Posted - 2007-08-30 : 00:44:09

learnt the stuff()

Declare @phone varchar(20)
Set @phone = '90939067440000'
select '(' + left(@phone,3) + ')' + substring(@phone,4,3) + '-' + substring(@phone,7,4) + 'x' + right(@phone,4)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-08-31 : 00:24:38

quote:
Originally posted by sbalaji

learnt the stuff()

Declare @phone varchar(20)
Set @phone = '90939067440000'
select '(' + left(@phone,3) + ')' + substring(@phone,4,3) + '-' + substring(@phone,7,4) + 'x' + right(@phone,4)

That only works if there is exactly 14 digits...

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-31 : 02:14:28

If you want to show formatted number in VB, then it is as easy as

Print Format(Phone_no,"(###)###-####x###")

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources