我正在尝试编写一个用空格替换特殊字符的查询。下面的代码有助于识别行。(字母数字字符、逗号和空格有效):
SELECT columnA
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'
如何将替换功能集成到 select 语句中,以便结果集中除字母数字、逗号和空格之外的所有字符都替换为“”(空格)。这个不行:
SELECT replace(columnA,'%[^a-Z0-9, ]%',' ')
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'
如果您保证只使用美国英语字母表的 26 个字母(大写和小写版本),那么可以肯定,您可以使用
LIKE
和/或PATINDEX
简单的范围表示法[a-z]
(您不会使用不区分大小写的排序规则时需要使用大写的“Z”)。但是,如果您可能会得到在 en-US 字母表中找不到但在各种代码页/
VARCHAR
数据整理中可用的字符(例如Þ
= 拉丁大写“Thorn”=SELECT CHAR(0xDE)
),那么您可能需要将这些字符包含在字符类中:[a-z0-9, Þ]
. 当然,这些额外的字符是基于每个代码页的。此外,请注意排序规则类型(SQL Server 与 Windows)和敏感度设置(大小写、重音等敏感与不敏感)都会影响特定范围内包含的字符。例如,SQL Server 排序规则以与 Windows 排序规则相反的顺序对大写和小写字母进行排序。意思是,假设两种排序规则都区分大小写,一种会做
AaBb...
,另一种会做aAbB...
。效果将a
是在其中一个范围内A-Z
,但不在另一个范围内。鉴于a-Z
的值为65_BIN
和__BIN2
_BIN
A
a
是 97,因此它是 97 到 65 的无效范围;-)。这里有太多的变化可以举出例子,所以我会尽快在我的博客上发布一个详细的解释(然后会用它的链接更新它)。但是,如果您要严格只接受美国英语字符(即使您可能会从其他语言中获得有效字母),那么您最好的选择可能是使用以下模式和排序规则:现在,如果您正在支持
NVARCHAR
数据并且可以从各种语言中获取“单词”字符,那么 T-SQL 将没有多大帮助,因为它没有真正的方法来区分这些东西。在这种情况下,您应该使用正则表达式 (RegEx)——特别是Replace
方法/函数——而这些只能通过 SQLCLR 获得。下面显示了一个替换几个“特殊”字符的示例,但将所有有效字母保留在至少一种语言中:回报:
RegEx 表达式意味着:
\W
= 一个正则表达式“转义”,意思是“任何非单词字符”\p{Pc}
=“标点符号,连接符”的Unicode“类别”(这仅用于匹配,因为此“类别”被\W
转义明确排除)-[,]
= 类减法(这需要将逗号从匹配中排除为“特殊”,因为它们包含在\W
转义中)您只需发出以下命令即可更新表:
请注意,对于这些示例,我使用了我创建的免费版SQL# SQLCLR 函数库中提供的两个函数(但同样,这些都是免费的)。另请注意,由于使用
NVARCHAR(4000)
而不是NVARCHAR(MAX)
参数类型,我使用了更快的“4k”版本。如果您的数据正在使用NVARCHAR(MAX)
,则只需从函数名称中删除“4k”。另请参阅:
我在这里有一个帖子做类似的事情。
基本上,我使用递归 CTE 一次又一次地循环替换一个“坏”字符。我正在使用 STUFF 去除 1 个字符(尽管您可以使用它来替换空格)和 PATINDEX 来查找我要删除的字符的位置。你可以稍微修改它来做你正在寻找的东西。然而,它创建了一个“好”列表,它实际上并没有更新现有列表。
您应该能够修改底部以进行更新而不仅仅是查询,但我还没有真正尝试过。我很确定它看起来像这样:
就可扩展性而言,我在 30 秒内返回了大约 17 万行清理过的行。再次不确定是否要进行更新,但这是在我的笔记本电脑上,它只有 6GB 内存,速度相当慢。