CODEWITHSUNDEEP
sqlregexoracle-database
minerals
minerals

Reputation: 1315

SQL exclusion regexp does not work, why?

I have some sentences in db. I want to select the ones that don't have urls in them.

So what I do is

select ID, SENTENCE
from SENTENCE_TABLE
where regexp_like(SENTENCE, '[^http]');

However after the query is executed the sentences that appear in the results pane still have urls. I tried a lot of other combinations without any success. Can somebody explain or give a good link where it is explained how regexps actually work in SQL.

How can I filter(exclude) actual words in db with SQL query?

Upvotes: 0

Views: 2111

Answers (2)

Ben
Ben

Reputation: 52853

You're over-complicating this. Just use a standard LIKE.

select ID, SENTENCE
  from SENTENCE_TABLE
  where SENTENCE not like '%http%';

regexp_like(SENTENCE, '[^http]') will match everything but h, t and p separately. I like the PSOUG page on regular expressions in Oracle but I would also recommend reading the documentation.

To respond to your comment you can use REGEXP_LIKE, there's just no point.

select ID, SENTENCE
  from SENTENCE_TABLE
 where not regexp_like(SENTENCE, 'http');

This looks for the string http rather than the letters individually.

Upvotes: 4

Anirudha
Anirudha

Reputation: 32787

[^http] would match any character except h or t or t or p..So this would match any string that doesn't contain h or t or t or p anywhere in the string

It should be where not regexp_like(SENTENCE, '^http');..this would match anything that doesn`t start with http

Upvotes: 3

Related Questions