Oracle regexp_like 否定特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16612315/
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
Oracle regexp_like negating special char
提问by mbow
I'm using Oracle 10g trying to EXCLUDE entries that contain a -or a _with a caret in the character class in Oracle 10g. I can find the entries containing dash or underscore through:
我正在使用 Oracle 10g 尝试排除在 Oracle 10g 的字符类中包含 a-或 a 的条目_。我可以通过以下方式找到包含破折号或下划线的条目：
WITH example
AS (SELECT 'AAAA-1' n FROM DUAL
UNION
SELECT 'AAAAA_1' FROM DUAL
UNION
SELECT 'AAAA' FROM DUAL)
SELECT *
FROM example
WHERE REGEXP_LIKE (n, '[_\-]')
I know I can get by with using NOT but how do I negate this with a caret (^)? I've tried [^_\-]which returns everything, [^[_\-]]which returns nothing, and [^(_\-)]which is invalid.
我知道我可以通过使用 NOT 但我如何用插入符号否定它(^)？我试过[^_\-]哪些返回所有内容， [^[_\-]]哪些不返回任何内容，[^(_\-)]哪些无效。
采纳答案by Egor Skriptunoff
回答by femtoRgon
Try:
尝试：
^[^_-]*$
I believe that [^_-]matches anything, because it is looking for Anycharacter that is anything other than '_' or '-'. Similar to the opposite that works, [_-], which finds any character, anywhere in the string, that iseither a '-' or '_'.
我相信它[^_-]匹配任何东西，因为它正在寻找除“_”或“-”以外的任何字符。类似的作品，相反[_-]，它发现任何字符，在string中任何地方，那就是无论是“ - ”或“_”。
To change that, accept any number of character matching you character class [^_-], and surround with ^(start of line) and $(end of line).
要更改它，请接受与您的字符类匹配的任意数量的字符[^_-]，并用^(行首) 和$(行尾) 括起来。
回答by tbone
I would probably use NOT regexp_like, which is more clear. But since you mentioned you don't want to use NOT, then I would probably use this (again, more clear imo):
我可能会使用 NOT regexp_like，这更清楚。但是既然你提到你不想使用 NOT，那么我可能会使用这个（同样，更清晰的 imo）：
select 'Does NOT contain dash or underscore' as val
from dual
where regexp_instr('ABC123', '[-_]') = 0;
I'm sure you'd have about 20 different regexp versions soon ;-)
我相信你很快就会有大约 20 个不同的正则表达式版本;-)
If you care about special treatment of empty strings(nulls), use a simple nvl:
如果您关心空字符串（空值）的特殊处理，请使用简单的 nvl：
nvl(regexp_instr('', '[-_]'),0) = 0;
I mention this because using regexp_like does not allow for this (nvl isn't a relational operator, but in this case we're comparing instr to a number, so we can use nvl on the instr (left hand) part.
我提到这一点是因为使用 regexp_like 不允许这样做（nvl 不是关系运算符，但在这种情况下，我们将 instr 与数字进行比较，因此我们可以在 instr（左手）部分使用 nvl。
But granted this depends on whether you want to say anything about the existence or non-existence of some characters in a null ;-)
但是，这取决于您是否想说一些关于 null 中某些字符的存在或不存在的信息；-)
