SQL Server 2008 查询以查找列中包含非字母数字字符的行 [英] SQL Server 2008 query to find rows containing non-alphanumeric characters in a column

问题描述

几周前我自己实际上被问到这个问题，而我确切地知道如何使用 SP 或 UDF 来做到这一点，但我想知道如果没有这些方法，是否有一种快速简便的方法来做到这一点.我假设有，但我找不到它.

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.

我需要说明的一点是，尽管我们知道允许使用哪些字符(az、AZ、0-9)，但我们不想指定不允许使用的字符(#@!$等等...).此外，我们希望提取具有非法字符的行，以便将其列出给用户进行修复(因为我们无法控制输入过程，因此此时我们无能为力).

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 have the 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).

我之前已经浏览过 SO 和 Google，但找不到任何符合我要求的内容.我看过很多例子，它们可以告诉你它是否包含字母数字字符，或者不包含，但是可以在我没有在查询表单中找到的句子中提取撇号.

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.

另请注意，此 varchar 列中的值可以是 null 或 ''(空).

Please note also that values can be null or '' (empty) in this varchar column.

推荐答案

这不行吗?

SELECT * FROM TABLE
WHERE COLUMN_NAME LIKE '%[^a-zA-Z0-9]%'

设置

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 

结果

mycol
----------------------------------------
efg%^&hji
apostrophe ' in a sentence

这篇关于SQL Server 2008 查询以查找列中包含非字母数字字符的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！