<div>
You'd need to edit it slightly to return the format you want.<br />
<br />
Change this:<br />
<br />
return @vchCleanedPhoneNumber<br />
<br />
to<br />
<br />
return left(@vchCleanedPhoneNumbe<wbr />r, 3) + '-' + left(right(@vchCleanedPhon<wbr />eNumber, 6), 3) + '-' + right(@vchCleanedPhoneNumb<wbr />er, 4)<br />
<br />
This will only work if the number is always 10 digits.<br />
<br />
Lee<br />
<br />
<br />

</div>


You'd need to edit it slightly to return the format you want.

Change this:

return @vchCleanedPhoneNumber

to

return left(@vchCleanedPhoneNumber, 3) + '-' + left(right(@vchCleanedPhoneNumber, 6), 3) + '-' + right(@vchCleanedPhoneNumber, 4)

This will only work if the number is always 10 digits.

Lee




<div>
<div class="content wysiwyg-content">
I have a varchar(100) field in SQL Server 2008 with phone numbers. &nbsp;There is no set format, some are 1231234567, some are (123)123-4567, some are 123.123.4567, and various other iterations.<br />
<br />
I need a query to just find the 10 numbers in the field (and ignore spaces and special charcters) and update them like this 123-123-4567.
</div>
</div>


I have a varchar(100) field in SQL Server 2008 with phone numbers.  There is no set format, some are 1231234567, some are (123)123-4567, some are 123.123.4567, and various other iterations.

I need a query to just find the 10 numbers in the field (and ignore spaces and special charcters) and update them like this 123-123-4567.

SQL Query to clean up phone numbers

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.