MS SQL 中如何高效将字符串内多类不同字符统一替换为下划线

现有方案的优化替代 #

你当前使用嵌套REPLACE的方式在替换字符较少时可以正常运行，但当需要替换的非法文件名字符较多时，会存在嵌套层级多、维护性差的问题，推荐优先使用原生TRANSLATE函数实现，该函数支持单次遍历完成多字符批量替换，性能和可读性都更高：

DECLARE @string nvarchar(MAX)
DECLARE @new_string nvarchar(MAX)
-- 所有需要替换为下划线的字符，后续新增直接往该字符串追加即可
DECLARE @invalid_chars nvarchar(100) = ' ./='
-- 生成和非法字符等长的下划线替换串
DECLARE @replace_chars nvarchar(100) = REPLICATE('_', LEN(@invalid_chars))

SET @string = 'This is.my string/That Needs=cleaning'
SET @new_string = TRANSLATE(@string, @invalid_chars, @replace_chars)

SELECT @new_string

注：TRANSLATE函数支持 SQL Server 2016 SP2、SQL Server 2017 及以上版本，以及 Azure 系 SQL 产品。

正则实现方案 #

MS SQL 早期版本没有内置正则替换能力，不同场景可选择对应实现：

• 如果你使用 SQL Server 2022 及以上版本、或者 Azure SQL 系列产品，可以直接调用内置的REGEXP_REPLACE函数，特别适合文件名清理这类需要批量排除大量非法字符的场景：

-- 示例：将所有非字母、数字、下划线、点、横杠的字符统一替换为下划线，覆盖绝大多数文件名非法字符
SET @new_string = REGEXP_REPLACE(@string, '[^a-zA-Z0-9_.-]', '_')

• 如果你使用更早版本的SQL Server，需要正则能力的话需要部署CLR自定义函数，但该方案需要服务器开启CLR权限，多数生产环境默认不支持。

选型建议 #

• 替换字符少于10个、且数据库版本支持TRANSLATE：优先选TRANSLATE方案，无额外权限要求，性能最优
• 清理文件名这类非法字符多、规则灵活的场景，且使用高版本SQL Server：优先选内置正则替换方案
• 低于2016 SP2的老版本SQL Server：继续使用嵌套REPLACE方案，也可以自行封装标量函数简化调用

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