Can anyone help me with a little problem I can't solve. I am trying to update a phone_number column to replace any character that is not 0-9 with an empty string ie:
01234-567-890 (result i want = 01234567890)
012345 6789ext (result i want = 0123456789)
n/a (result i want = )
...12345..... (result i want = 12345)
i can identify which records have have non-numeric values in the phone number using...
but I want to update this table and remove non-numeric characters in the phone_number field something along the lines of....
Any help would be greatly appreciated
OK just for fun, here's a TSQL method, using Jeff Moden's Tally table, and a SQL2005 (and later) trick for concatenating the results back
answered Mar 30 '10 at 01:40 PM
Kev Riley ♦♦
Select (Replace(Replace(phone_number, '-', ''), ' ', '') from phone_table
answered Jun 06 '12 at 05:04 PM
Do you have a lot of different non-numeric characters? If not I would simply go for
if there are lots of different ones and you need to repeat this process regularly then you might need to get involved with regex, functions and even clr ...
answered Mar 30 '10 at 12:06 PM