By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create TABLE TEST(S_NO NUMBER, IDENTITY VARCHAR2(50));
BEGIN
INSERT INTO TEST VALUES(1, 'aa.sam.diamond.star');
INSERT INTO TEST VALUES(2, 'bb.test.diamond.star');
INSERT INTO TEST VALUES(3, 'bc.sam.gold.star');
INSERT INTO TEST VALUES(4, 'aa.sam.diamond.star');
INSERT INTO TEST VALUES(5, 'bb.test.gold.star');
END;
/
1 rows affected
SELECT * FROM TEST
WHERE NOT REGEXP_LIKE(IDENTITY, '(aa.sam.|bb.test.)');
| S_NO | IDENTITY |
|---|---|
| 3 | bc.sam.gold.star |