Friday, September 11, 2009

REGEXP_LIKE

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)');

No comments: