CODEWITHSUNDEEP
sqloracle-database
MOHAMED AZARUDEEN
MOHAMED AZARUDEEN

Reputation: 171

Select Query - Get the Records where the columns should not start with two patterns - SQL ORACLE

I am having table Customer which has two columns "Name" and "Identity".

I want to get the rows where the column "Identity" does not start with following two patterns.

  1. aa.sam.
  2. bb.test.

The table looks like

S.no.    Name.      Identity
--------------------------------
 1      Asif Ali    aa.sam.diamond.star
 2      Daniel Sam. bb.test.diamond.star
 3      Rob Boucher bc.sam.gold.star
 4      Anil        aa.sam.diamond.star
 5      CCC         bb.test.gold.star

I expect to get the final Result should be following:

 S.no.    Name.      Identity
--------------------------------
 3      Rob Boucher bc.sam.gold.star
 4      Anil        aa.sam.diamond.star

please help someone to solve my problem

Thanks in advance

Upvotes: 0

Views: 282

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You may use a regular expression like below also -

SELECT *
  FROM TEST
 WHERE NOT REGEXP_LIKE(IDENTITY, '(aa.sam.|bb.test.)');

P.S. - There might be a better regexp for this but I am able to manage only this.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

It seems as if you just need a couple of not like clauses.

select *
  from customer
 where identity not like 'aa.sam.%'
   and identity not like 'bb.test.%'

Upvotes: 1

Related Questions