sql query to fix phone number problem in table

Question

I have a table which contains a phone number column. There is no restrictions on how to enter the phone numbers. Currently the phone numbers are in the following format

123-456-7890
(123)-456-7890
1234567890

I would like to update the table and bring all phone numbers in 123-456-7890 format. I have over 20k records. Can I do that using SQL Query or I have to use regular expression in ASP or PHP?

Edit: Note best answer is for modified question, with phone number (123)-456-78790 changed to (123)456-7890

Answer 1

If they are strictly in one of those 3 formats, you can do it in SQL easy enough by using SUBSTRING and testing the LEN of each item.

If there are other formats, I'd suggest doing this in a language that is better at text-manipulation, such as .net.

Edit to add:

Given your comment that it'll only be those 3 formats for now, you can do this:

declare @t table (x varchar(20))
insert into @t 
select '123-456-7890'
union select '(123)456-7890'
union select '1234567890'

select 
    case 
      when len(x) = 10 then 
        substring(x, 1, 3) + '-' + substring(x, 4, 3) + '-' + substring(x, 7, 4)
      when len(x) = 13 then
        substring(x, 2, 3) + '-' + substring(x, 6, 8)
      else x
    end
from @t