SQL Script that removes all non-numeric numbers

Question

I need a SQL script that will remove all non numeric character for each record in a SQL table column. The column is used to hold phone numbers and there are many different formats in the database which is why i just want it to update the table remove all the non-numeric character and change it a number. Does anyone have a good one-time script that can update all of the records in the table?

Edit:

It looks like the only character i really need to replace is "-", "(" and ")"

A non-numeric number is a any character that is not a 0,1,2,3,4,5,6,7,8, or 9. — user3788671, Sep 15 '14 at 20:09
Do you actually have non numeric characters in there apart from `(`,`)` and `+`? Unless you want to install a CLR Regex or a slow scalar UDF will be easier if we can assume specific characters! — Martin Smith, Sep 15 '14 at 20:09
It looks like the only character i really need to replace is "-", "(" and ")" — user3788671, Sep 15 '14 at 20:10
Please post a few samples of numbers you want to clean that are problematic, and the expected output format (number only? want it in varchar in a particular '-' separated format? Please clarify. — Jaaz Cole, Sep 15 '14 at 20:13
Actually I just looked to find an example to link to of using regex for any future searchers and came across this duplicate question so closed as dupe (also has an example of a scalar UDF approach) — Martin Smith, Sep 15 '14 at 20:22

score 2 · Accepted Answer · edited Sep 15 '14 at 20:15

2

As you only have a few problem characters you can just do a nested replace.

UPDATE T
SET PhoneNumber = REPLACE(REPLACE(REPLACE(PhoneNumber,'(',''),')',''),'-','')

_{See REPLACE()}

edited Sep 15 '14 at 20:15

canon

40,609
10
73
97

answered Sep 15 '14 at 20:13

Martin Smith

438,706
87
741
845

Thank you very much. I will mark in a couple of minutes – user3788671 Sep 15 '14 at 20:16

SQL Script that removes all non-numeric numbers

1 Answers1