Oracle SQL where regexp_like and not like

Question

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

The query looks correct to me. Can you provide some actual sample data from my_table? — Lukas Eder
– Lukas Eder, Commented Jul 23, 2012 at 10:40

APC · Accepted Answer · 2012-07-23 10:41:05Z

4

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.

answered Jul 23, 2012 at 10:41

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

I'll give this answer the tick as you confirmed that my query was correct and it was a problem with the data. It turns out that the records on the data leading spaces (eg ' 999RT') so I had to trim first.

Community · Accepted Answer · 2017-05-23 12:20:08Z

1

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jul 23, 2012 at 10:49

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Collectives™ on Stack Overflow

Oracle SQL where regexp_like and not like

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related