How to use LIKE and IN operators in one query?

Question

My simple sql statement:

SELECT USERNAME FROM ALL_USERS; 

But I want to filter system users from the result. I searched but could not find anything that Oracle provided; so I tried this expression and it does not work:

 select username from all_users where username not like '%SH%' or username not like '%SYS%' or username not in ('ANONYMOUS', 'DBSNMP', 'MGMT_VIEW', 'ORDPLUGINS', 'OUTLN', 'SI_INFORMATION_SCHEMA', 'WK_TEST', 'WKPROXY', 'XDB'); 

This does not work. How can I change my query to the desired result, or maybe there is something that provides the oracle for getting predefined system accounts?

Answer 1

A slight twist on the other answers: no (A or B or C) = not A, not B, not C, so what you probably originally wanted was:

 select username from all_users where not (username like '%SH%' or username like '%SYS%' or username in ('ANONYMOUS', 'DBSNMP', 'MGMT_VIEW', 'ORDPLUGINS', 'OUTLN', 'SI_INFORMATION_SCHEMA', 'WK_TEST', 'WKPROXY', 'XDB') ); 

Answer 2

Instead of OR you will need to use AND

 select username from all_users where username not like '%SH%' AND username not like '%SYS%' AND username not in ('ANONYMOUS', 'DBSNMP', 'MGMT_VIEW', 'ORDPLUGINS', 'OUTLN', 'SI_INFORMATION_SCHEMA', 'WK_TEST', 'WKPROXY', 'XDB'); 

However, it will also filter out legitimate non-system users whose names contain SYS or end in SH . JOSH user will be lost.

Answer 3

replace or with AND , I think it will start working.

However, a better approach might be to have a flag indicating whether it is a system user or not. A more sophisticated approach may be to have a separate rights table that will have many different relationships with the user table.

Answer 4

You can try the following:

  select username from all_users where username not like '%SH%' AND username not like '%SYS%' INTERSECT select username from all_users where username not in ('ANONYMOUS', 'DBSNMP', 'MGMT_VIEW', 'ORDPLUGINS', 'OUTLN', 'SI_INFORMATION_SCHEMA', 'WK_TEST', 'WKPROXY', 'XDB');