I needed to scrub a column of power meters. I needed to remove certain characters ( [space], hypens, / and . ) that had found their way into the system.
, REPLACE(REPLACE(REPLACE(REPLACE(dbo.node.power_meter, ' ', ''), '-',''), '/', ''), '.', '') AS better_power_meter
This was fast enough on a under a hundred thousand rows. You might want to try using CLR with RegEx if you have billions to clean.