SQL Server 2008 查询以查找列中包含非字母数字字符的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1919757/
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 query to find rows containing non-alphanumeric characters in a column
提问by Jay
I was actually asked this myself a few weeks ago, whereas I know exactly how to do this with a SP or UDF but I was wondering if there was a quick and easy way of doing this without these methods. I'm assuming that there is and I just can't find it.
几周前我自己实际上被问到这个问题，而我确切地知道如何使用 SP 或 UDF 来做到这一点，但我想知道如果没有这些方法，是否有一种快速简便的方法来做到这一点。我假设有，但我找不到它。
A point I need to make is that although we know what characters are allowed (a-z, A-Z, 0-9) we don't want to specify what is not allowed(#@!$ etc...). Also, we want to pull the rows which havethe illegal characters so that it can be listed to the user to fix (as we have no control over the input process we can't do anything at that point).
我需要说明的一点是，虽然我们知道允许使用哪些字符（az、AZ、0-9），但我们不想指定不允许使用的字符（#@!$ 等...）。此外，我们希望提取包含非法字符的行，以便将其列出给用户进行修复（因为我们无法控制输入过程，因此此时我们无能为力）。
I have looked through SO and Google previously, but was unable to find anything that did what I wanted. I have seen many examples which can tell you if it contains alphanumeric characters, or doesn't, but something that is able to pull out an apostrophe in a sentence I have not found in query form.
我以前浏览过 SO 和谷歌，但找不到任何可以满足我想要的东西。我看过很多例子，它们可以告诉你它是否包含字母数字字符，或者不包含，但是能够在我没有在查询形式中找到的句子中提取撇号的东西。
Please note also that values can be nullor ''(empty) in this varcharcolumn.
另请注意，此列中的值可以是null或''（空）varchar。
回答by beach
Won't this do it?
这不行吗？
SELECT * FROM TABLE
WHERE COLUMN_NAME LIKE '%[^a-zA-Z0-9]%'
Setup
设置
use tempdb
create table mytable ( mycol varchar(40) NULL)
insert into mytable VALUES ('abcd')
insert into mytable VALUES ('ABCD')
insert into mytable VALUES ('1234')
insert into mytable VALUES ('efg%^&hji')
insert into mytable VALUES (NULL)
insert into mytable VALUES ('')
insert into mytable VALUES ('apostrophe '' in a sentence')
SELECT * FROM mytable
WHERE mycol LIKE '%[^a-zA-Z0-9]%'
drop table mytable
Results
结果
mycol
----------------------------------------
efg%^&hji
apostrophe ' in a sentence
回答by Adriaan Stander
Sql server has very limited Regex support. You can use PATINDEX with something like this
Sql 服务器对正则表达式的支持非常有限。您可以将 PATINDEX 与这样的内容一起使用
PATINDEX('%[a-zA-Z0-9]%',Col)
Have a look at PATINDEX (Transact-SQL)
回答by wwmbes
I found thispage with quite a neat solution. What makes it great is that you get an indication of what the character is and where it is. Then it gives a super simple way to fix it (which can be combined and built into a piece of driver code to scale up it's application).
我发现这个页面有一个非常巧妙的解决方案。它的伟大之处在于您可以了解角色是什么以及它在哪里。然后它提供了一种超级简单的修复方法（可以将其组合并构建到一段驱动程序代码中以扩展其应用程序）。
DECLARE @tablename VARCHAR(1000) ='Schema.Table'
DECLARE @columnname VARCHAR(100)='ColumnName'
DECLARE @counter INT = 0
DECLARE @sql VARCHAR(MAX)
WHILE @counter <=255
BEGIN
SET @sql=
'SELECT TOP 10 '+@columnname+','+CAST(@counter AS VARCHAR(3))+' as CharacterSet, CHARINDEX(CHAR('+CAST(@counter AS VARCHAR(3))+'),'+@columnname+') as LocationOfChar
FROM '+@tablename+'
WHERE CHARINDEX(CHAR('+CAST(@counter AS VARCHAR(3))+'),'+@columnname+') <> 0'
PRINT (@sql)
EXEC (@sql)
SET @counter = @counter + 1
END
and then...
进而...
UPDATE Schema.Table
SET ColumnName= REPLACE(Columnname,CHAR(13),'')
Credit to Ayman El-Ghazali.
归功于 Ayman El-Ghazali。
