在 SQL Server 2008 中查找列中的所有特殊字符 [英] Find all special characters in a column in SQL Server 2008

问题描述

我需要在SQL Server 2008 中查找列中所有特殊字符的出现.所以，我不在乎 A, B, C ... 8, 9, 0，但我在乎 !、@、&、 等.

I need to find the occurrence of all special characters in a column in SQL Server 2008. So, I don't care about A, B, C ... 8, 9, 0, but I do care about !, @, &,, etc.

在我看来，最简单的方法是排除 A, B, C, ... 8, 9, 0，但是如果我写了一个语句来排除这些，我会错过具有 ! 和 A 的条目.因此，在我看来，我必须获得每个非字母/非数字字符的列表，然后使用 LIKE 和 运行 SELECT通配符 限定符.

The easiest way to do so, in my mind, would exclude A, B, C, ... 8, 9, 0, but if I wrote a statement to exclude those, I would miss entries that had ! and A. So, it seems to me that I would have to get a list of every non-alphabet / non-number character, then run a SELECT with a LIKE and Wildcard qualifiers.

这是我要运行的:

SELECT Col1
FROM TABLE
WHERE Col1 LIKE ('!', '@', '#', '$', '%'....)

但是，我不认为您可以运行多个预选赛，对吗?有什么办法可以做到这一点吗?

However, I don't think you can run multiple qualifiers, can you? Is there a way I could accomplish this?

推荐答案

否定是你的朋友:

SELECT Col1
FROM TABLE
WHERE Col1 like '%[^a-Z0-9]%'

这表示您想要 Col1 由任意数量的字符组成的任何行，然后是集合 a-Z0-9 中的一个字符不是，然后是任意数字字符.

Which says that you want any rows where Col1 consists of any number of characters, then one character not in the set a-Z0-9, and then any number of characters.

如果您有区分大小写的排序规则，请务必使用包含大写和小写 A、a、Z 的范围> 和 z，这是我给出的(最初我用错误的方式.a 出现在 A 之前.Z 在 z 之后)

If you have a case sensitive collation, it's important that you use a range that includes both upper and lower case A, a, Z and z, which is what I've given (originally I had it the wrong way around. a comes before A. Z comes after z)

或者，换句话说，你可以把你原来的 WHERE 写成:

Or, to put it another way, you could have written your original WHERE as:

Col1 LIKE '%[!@#$%]%'

但是，正如您所观察到的，您需要知道[] 中包含的所有字符.

But, as you observed, you'd need to know all of the characters to include in the [].

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