CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Tom
Tom

Reputation: 12998

Oracle SQL where regexp_like and not like

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

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

Upvotes: 1

Views: 14593

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220842

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

Upvotes: 1

APC
APC

Reputation: 146219

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.

Upvotes: 4

Related Questions