CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-like
neocorp
neocorp

Reputation: 569

How to use LIKE and IN operators in one query?

My simple sql statement is this:

SELECT USERNAME FROM ALL_USERS;

But I want to filter system users from the result. I searched but couldn't find anything that Oracle provided; so I tried a statement like this and it doesn't 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 doesn't work. How should I modify my query for the desired output or maybe there is something oracle provides to get predefined system accounts?

Upvotes: 4

Views: 1769

Answers (4)

Somnath Muluk
Somnath Muluk

Reputation: 57656

You can try this: 

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

Upvotes: 0

user359040
user359040

Reputation:

A slight twist on the other answers: not (A or B or C) = not A and not B and 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')
           );

Upvotes: 5

Zohaib
Zohaib

Reputation: 7116

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

However a better approach might be to have a flag indicating whether its a system user or not. A more sophosticated approach might be to have seperate table for rights that will have many to many relationship with User table.

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270609

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, this will also filter legitimate non-system users whose names contain SYS or end in SH. User JOSH will be lost.

Upvotes: 4

Related Questions