1. Using REPLACE

A very common method is to use multiple REPLACE functions in your UDPATE
statement. This is simple, easy to understand, but may become  clumsy, if
you have too many invalid characters in your string

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '044-(212)-976-8789'
SELECT REPLACE(
              REPLACE(
                      REPLACE(@BadStr,
                       '-', SPACE(0)),
              '(', SPACE(0)),
       ')', SPACE(0))

2. CASE expressions

If the string is limited in length, you can use a series of CASE
expressions,
however this also suffers from the drawbacks described above.

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '7%6@5'

SELECT CASE WHEN SUBSTRING(@BadStr, 1, 1) LIKE '[0-9]'
            THEN SUBSTRING(@BadStr, 1, 1) ELSE '' END +
       CASE WHEN SUBSTRING(@BadStr, 2, 1) LIKE '[0-9]'
            THEN SUBSTRING(@BadStr, 2, 1) ELSE '' END +
       CASE WHEN SUBSTRING(@BadStr, 3, 1) LIKE '[0-9]'
            THEN SUBSTRING(@BadStr, 3, 1) ELSE '' END +
       CASE WHEN SUBSTRING(@BadStr, 4, 1) LIKE '[0-9]'
            THEN SUBSTRING(@BadStr, 4, 1) ELSE '' END +
       CASE WHEN SUBSTRING(@BadStr, 5, 1) LIKE '[0-9]'
            THEN SUBSTRING(@BadStr, 5, 1) ELSE '' END

3. T-SQL UPDATE extn.

Another intuitive way of doing such conversions is to use another table
with values to be replaced and corresponding new values as the columns like:

SELECT *
  INTO #temp
  FROM (
        SELECT ' ', SPACE(1) UNION ALL
        SELECT '*', SPACE(0) UNION ALL
        SELECT '%', SPACE(0) UNION ALL
        SELECT '-', SPACE(0) UNION ALL
        SELECT '$', SPACE(0)             -- add as many as needed
        ) D(old, new)

Now do:

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '345%*6%3$6-457'
UPDATE #temp
   SET @BadStr = REPLACE(@BadStr, Old, New)
 PRINT @BadStr

You can make this a permanent table and use it for general data cleanup
routines and can come in handy with a UDF in SQL 2000.

4. Scalar UDF

In SQL 2000, you can use a scalar UDF which can remove all the expleteves
from your input string as follows:

CREATE FUNCTION dbo.ufn_onlydigits (@StrVal AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
      WHILE PATINDEX('%[^0-9]%', @StrVal) > 0
            SET @StrVal = REPLACE(@StrVal,
                SUBSTRING(@StrVal,PATINDEX('%[^0-9]%', @StrVal),1),'')
      RETURN @StrVal
END
GO

Usage:
SELECT dbo.ufn_onlydigits('8asdf7%87^A8876-*S')

In other SQL versions, depending on what you are doing, you can also make
the above logic into a stored procedure with an OUTPUT parameter.