Need format for phone numbers 3

[starbase59]

How do I format a column of phone numbers? For example make them all like:
(###)###-#### or ###-###-####.Right now the data type is nvarchar (14). Is there a function for this?

 

[ClaireHCM]

For (###)###-####
select '('+left(colname,3)+')'+substring(colname,4,3)+'-'+substring(colname,7,4) from tablename

For ###-###-####
Select left(colname,3)+'-'+substring(colname,4,3)+'-'+substring(colname,7,4) from tablename

 

[starbase59]

thank you Claire. Here's more of my problem: the phone numbers currently in this column are in various formats, such as (123)456-7889 or 1234567889 or 123 456-7889. If I choose Left(colname,3)...I don't get numbers, necessarily. How do I get rid of the parens?

 

[sunila7]

Hi,

Use Replace function to replace the characters u want to remove...
something like this and then use Claire's suggestion

select '('+left(colname,3)+')'+substring(colname,4,3)+'-'+substring(colname,7,4) from
(SELECT REPLACE(REPLACE(REPLACE(REPLACE(colname,' ',''),')',''),'(',''),'-','') colname FROM TBLname) TBL

Hope it helps...
Sunil

 

[zhavic]

For removing other characters than numbers you can also use function that I post in thread183-543157

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ClaireHCM]

No offense,If the symbols which need to be removed are only 3 or 4.Really we dont need to use loop.It's not that efficient.

 

[zhavic]

You are right, ClaireHsu, but I post it as another example, it can be usefull, when don't now all the other characters, that can be in the number, such as overstriking or something like this

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.