select ename,sal,deptno from scott.emp
where
ename not like '%ED%'
and
ename not like '%CD%'
and
ename not like '%FD%'
and
ename not like '%RD%'
and
ename not like '%SD%'
and
ename not like '%KD%' ;
you can change above query very simply--------
select ename,sal,deptno
from scott.emp
where not REGEXP_LIKE(ename,'RD|ED|CD|FD|SD|KD')
select ename,sal,deptno from emp
where not regexp_like(ename, '(E|C|F|R|S|K)D');
Also you can do,
SELECT ename, sal, deptno
FROM emp_test
WHERE NOT REGEXP_LIKE (ename, '[ECFRSK]D');
A longer format using like is,
select ename, sal, deptno
from emp, table (sys.dbms_debug_vc2coll (
'ED',
'CD',
'FD',
'RD',
'SD',
'KD'))
where ename not like '%' || column_value || '%';
Or,select ename,sal,deptno from emp
where not regexp_like(ename, '(ED|CD|FD|RD|SD|KD)');
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment