or another version: a scalar function that would remove anything that was not A-Z/numeric:

same basic logic, i think:

select

id,

name,

dbo.StripNonAlphaNumeric(name) As Fixed

from my_user_table

If that's cleaning up the results correctly, a simple replace should fix it:

UPDATE my_user_table

SET name = StripNonAlphaNumeric(name)

and the function definition:

CREATE FUNCTION StripNonAlphaNumeric(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;WITH tally (N) as

(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

CASE

--ascii numbers are 48(for '0') thru 57 (for '9')

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57

THEN SUBSTRING(@OriginalText,Tally.N,1)

--ascii upper case letters A-Z is 65 thru 90

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 65 AND 90

THEN SUBSTRING(@OriginalText,Tally.N,1)

--ascii lower case letters a-z is 97 thru 122

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 97 AND 122

THEN SUBSTRING(@OriginalText,Tally.N,1)

ELSE '' END

FROM tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END