Oracle SQL where regexp_like 和 not like
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11610815/
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 SQL where regexp_like and not like
提问by Tom
I would like to query a table where I am comparing against a regular expression and then of those results I want to filter out any that begin with 999
我想查询一个表，我在其中与正则表达式进行比较，然后在这些结果中我想过滤掉以 999 开头的任何结果
Here's the query I have, but this returns no results:
这是我的查询，但这没有返回任何结果：
Select * FROM my_table
WHERE REGEXP_LIKE(my_row, '[a-zA-Z]')
AND my_row NOT LIKE '999%'
Any ideas where I'm going wrong with this? I know that I should be getting several results.
任何想法我哪里出错了？我知道我应该得到几个结果。
Sample date would be like the following...
示例日期如下所示...
my_row
______
12345
45673
G12354
1234B
999RT
回答by APC
Given this test data:
鉴于此测试数据：
create table my_table (id number, my_row varchar2(10));
insert into my_table values (1, '7878')
/
insert into my_table values (2, 'THIS')
/
insert into my_table values (3, 'and this')
/
insert into my_table values (4, '999NOTthis')
/
Your query returns these results:
您的查询返回以下结果：
ID MY_ROW
-- ------
2 THIS
3 and this
Here's a SQL Fiddle to prove it.
These seem to be the rows we would expect to be returned by your logic so the problem is clearly in your data.
这些似乎是我们希望您的逻辑返回的行，因此问题显然出在您的数据中。
回答by Lukas Eder
Given your sample data, this query:
鉴于您的示例数据，此查询：
with my_table as (
select '12345' as my_row from dual union all
select '45673' from dual union all
select 'G12354' from dual union all
select '1234B' from dual union all
select '999RT' from dual
)
select * from my_table
where regexp_like(my_row, '[a-zA-Z]')
and my_row not like '999%'
Yields
产量
MY_ROW
------
G12354
1234B
So I can second APC's answer, saying that the problem is elsewhere
所以我可以第二个APC的回答，说问题出在别处
