SQL中移除非ASCII字符（含HEX 99等）的解决方案求助

嘿，我太懂你现在的困扰了——把脏文本导入SQL后卡在非ASCII字符清理这一步，确实让人头疼。结合你的场景（SQL_Latin1_General_CP1_CI_AS排序规则，目标是仅保留ASCII 32-127范围的字符），我给你几个纯SQL的解决方案，应该能完美解决你的问题：

方案1：优化自定义字符过滤函数 #

你之前的ufn_CleanText返回?????，是因为没正确处理UTF-8编码的多字节非ASCII字符（比如你转储里的C3 A9是é的UTF-8编码）。试试这个改进版函数，它会逐个校验字符的Unicode值，精准保留有效ASCII字符：

CREATE FUNCTION dbo.ufn_CleanValidASCII(@Input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Output NVARCHAR(MAX) = ''
    DECLARE @Index INT = 1
    DECLARE @Char NCHAR(1)

    WHILE @Index <= LEN(@Input)
    BEGIN
        SET @Char = SUBSTRING(@Input, @Index, 1)
        -- 只保留ASCII 32（空格）到127（~）的字符
        IF UNICODE(@Char) BETWEEN 32 AND 127
            SET @Output = @Output + @Char
        SET @Index = @Index + 1
    END

    RETURN @Output
END

测试时直接调用即可：

SELECT dbo.ufn_CleanValidASCII('ENM / éææ¨FEE/\~`+=-') AS CleanedText

方案2：用COLLATE配合REPLACE批量替换 #

如果你的非ASCII字符种类不多，也可以针对性批量替换：

DECLARE @DirtyString NVARCHAR(MAX) = 'ENM / éææ¨FEE/\~`+=-'
SELECT 
    REPLACE(
        REPLACE(
            @DirtyString COLLATE SQL_Latin1_General_CP1_CI_AS,
            NCHAR(0x00E9), '' -- 移除é
        ),
        NCHAR(0x00E6), '' -- 移除æ
    ) AS CleanedText

这个方法需要提前明确要清理的字符，适合非ASCII字符范围固定的场景。

方案3：借助XML过滤非ASCII字符 #

更灵活的方法是利用SQL Server的XML解析能力，一次性移除所有非ASCII字符：

DECLARE @DirtyString NVARCHAR(MAX) = 'ENM / éææ¨FEE/\~`+=-'
SELECT 
    CAST(
        '<root><![CDATA[' + @DirtyString + ']]></root>' AS XML
    ).value('(/root/text())[1] cast as xs:string?', 'NVARCHAR(MAX)')
    COLLATE SQL_Latin1_General_CP1_CI_AS AS CleanedText

注意：如果你的字符串包含XML特殊字符（比如<、>、&），需要先做转义处理。

为什么之前的方法失效？ #

你提到的fn_StripCharacters或直接替换HEX字符没用，大多是因为这些非ASCII字符是UTF-8多字节编码，直接按单字节处理会拆分编码，导致残留或变成问号。上面的方法都是基于Unicode字符值判断，能精准识别并过滤。

优先试试方案1，它的通用性最强，适合各种非ASCII字符混杂的场景。

内容的提问来源于stack exchange，提问作者Adam Davies