I have a flat text file containing phone numbers.
They are formatted as ###,####### and I would like change it to
###-###-####.
What SQL statement would I need to use?
Thanks for the help.

The answer may depend on how you are loading the data from the file.
Take a look at the REPLACE and STUFF functions in Books Online. Those
functions could do it in TSQL.

declare @chvTelNumber varchar(20)
select @chvTelNumber = '###,#######'
select left(replace(@chvTelNumber,',','-'),7) + '-' +
right(@chvTelNumber,3)

http://sqlservercode.blogspot.com/

Typo should have been right 4

declare @chvTelNumber varchar(20)
select @chvTelNumber = '###,#######'
select left(replace(@chvTelNumber,',','-'),7) + '-' +
right(@chvTelNumber,4)

So here is what I have?
CREATE TABLE [donotcall].[dbo].[donotcall_txt] (
[Col001] char (11) NULL
)
declare @chvTelNumber varchar(20)
select @chvTelNumber = '###,#######'
select left(replace(@chvTelNumber,',','-'),7) + '-' +
right(@chvTelNumber,4)

or should I be using:

'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************

Function Main()
    DTSDestination("Col001") = DTSSource("Col001")
    Main = DTSTransformStat_OK
End Function

SQL wrote:
Show quoteHide quote

Personally I would do it in SQL since you can see what the data looks
like right away (preview)


http://sqlservercode.blogspot.com/

compare '002' and '00²'
Evaluation of conditions
complex and large triggers
Table design question on LARGE table
Query help