SQL Server 2008，搜索特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15792154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008, searching for special characters
提问by bigtunacan
I'm trying to locate some bad data that has been inserted into a table. Likely by someone doing a copy/paste from Word then inserting into the database.
我正在尝试查找已插入表中的一些错误数据。可能是有人从 Word 中复制/粘贴然后插入到数据库中。
I have seen the similar questions like Query for finding rows with special characters
我见过类似的问题，比如 Query for find rows with special characters
but this doesn't quite work for what I'm needing. Essentially I want to only return back a data set not including any standard characters and catch things such as an endash (just one example).
但这对我所需要的并不完全有效。本质上，我只想返回一个不包含任何标准字符的数据集并捕获诸如尾端之类的东西（只是一个例子）。
I have tried using something like this
我试过使用这样的东西
SELECT * FROM mytable WHERE email LIKE '%[^0-9a-zA-Z \-@\.]%'
but it returns back every single single record.
但它会返回每条记录。
EDIT
编辑
In case it is of benefit for anyone else that comes along later. Ultimately the issue I was having was due to the placement of the hyphen (-) character as was also noted by sgmoore below. I moved this to the beginning of my range just following the not (^) character.
以防它对以后出现的其他任何人都有好处。最终，我遇到的问题是由于连字符 (-) 字符的位置，正如下面的 sgmoore 所指出的。我将它移到我范围的开头，紧跟在 not (^) 字符之后。
Also, based on the info provided by gbn that LIKE is not actually using regexes I revisited the Microsoft documentation here SQL Server LIKE Statement. I was using the backslash character unnecessarily as an escape character due to my assumption it was the same as a regex. These were unnecessary, and apparently escape characters are only needed with wildcard characters. The doc I linked also mentions using an ESCAPE clause following the LIKE range to specify what character is to be used as an escape character e.g. WHERE percent_complete LIKE '%50!%' ESCAPE '!' would match a string that actually ends in 50% (50%, 150%).
此外，根据 gbn 提供的 LIKE 实际上并未使用正则表达式的信息，我在此处重新访问了 Microsoft 文档SQL Server LIKE Statement。由于我假设它与正则表达式相同，我不必要地使用反斜杠字符作为转义字符。这些是不必要的，显然转义字符只需要通配符。我链接的文档还提到在 LIKE 范围之后使用 ESCAPE 子句来指定要用作转义字符的字符，例如 WHERE percent_complete LIKE '%50!%' ESCAPE '!' 将匹配实际以 50% (50%, 150%) 结尾的字符串。
Here is what I ended up using to screen my email data for bad characters; for me it works, but it may not be complete for all cases.
这是我最终用来筛选电子邮件数据中的不良字符的方法；对我来说它有效，但它可能不适用于所有情况。
SELECT * FROM mytable WHERE email LIKE '%[^-0-9a-zA-Z_@.]%'
SELECT * FROM mytable WHERE email LIKE '%[^-0-9a-zA-Z_@.]%'
also if it is helpful, I needed to do something similar on a couple of other generic text fields; this far from comprehensive, but it narrowed my result set down to just a handful of records that I was then able to visually determine what I was looking for.
此外，如果有帮助，我需要在其他几个通用文本字段上做类似的事情；这远非全面，但它使我的结果集缩小到只有少数记录，然后我能够直观地确定我在寻找什么。
SELECT * from mytable WHERE text_field LIKE '%[^-0-9a-zA-Z @.''?:/,+&();_]%'
SELECT * from mytable WHERE text_field LIKE '%[^-0-9a-zA-Z @.''?:/,+&();_]%'
采纳答案by sgmoore
Try
尝试
SELECT * FROM mytable WHERE email LIKE '%[^0-9a-zA-Z @\.\-]%'
It would look like the position of the - sign on your version is causing problems.
看起来您的版本上 - 符号的位置导致了问题。
回答by gbn
Use double negatives
使用双重否定
... WHERE email NOT LIKE '%[^0-9a-zA-Z ,-@\.]%'
Sample data would be useful too
样本数据也很有用
回答by Gordon Linoff
Presumably, every email has a @character as well as .. You might try:
据推测，每封电子邮件都有一个@字符以及.. 你可以试试：
SELECT * FROM mytable WHERE email LIKE '%[^0-9a-zA-Z ,\]%'
If your original list is what you really want, then you need to escape -:
如果您的原始列表是您真正想要的，那么您需要转义-：
SELECT * FROM mytable WHERE email LIKE '%[^0-9a-zA-Z ,\-@\.]%'
