ASCII标准定义的控制字符，这些字符用肉眼是看不到的，也不能用简单的replace函数处理替换。

可以用以下两种方法处理

1.用字符编码替换

DECLARE @email VARCHAR(55)= 'johndoe@a!bc.com';
SELECT REPLACE(@email, CHAR(33), '');
 
2.动态检测，替换
当包含多个控制字符的处理，可以写一个实用的方法处理
CREATE FUNCTION [dbo].[ReplaceASCII](@inputString VARCHAR(8000))
RETURNS VARCHAR(55)
AS
     BEGIN
         DECLARE @badStrings VARCHAR(100);
         DECLARE @increment INT= 1;
         WHILE @increment <= DATALENGTH(@inputString)
             BEGIN
                 IF(ASCII(SUBSTRING(@inputString, @increment, 1)) < 33)
                     BEGIN
                         SET @badStrings = CHAR(ASCII(SUBSTRING(@inputString, @increment, 1)));
                         SET @inputString = REPLACE(@inputString, @badStrings, '');
                 END;
                 SET @increment = @increment + 1;
             END;
         RETURN @inputString;
     END;
GO