CODEWITHSUNDEEP
sqloracle-database
phileas fogg
phileas fogg

Reputation: 1933

How can I get rid of multiple "not like" clauses in my Oracle SQL?

I have a query where I'm grabbing the DDL for tables in a database.

There are externally mounted tables, temporary tables, and other tables that I don't want that information, so I have clauses like, ...where object_name not like 'ext_%'... but I have six of those clauses.

Is there some way of combining them so that I can do something like ...where object_name not like in (EXT_%, TMP_%,...)?

Sort of like how you can say, ...where id in (1,2,3).

Upvotes: 2

Views: 2507

Answers (3)

hypercrypt
hypercrypt

Reputation: 15376

How about this:

...
WHERE SUBSTR(object_name, 1, 4) NOT IN ('EXT_', 'TMP_', ...)

Upvotes: 3

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

If thay are all that pattern XXX_%, you could strip the first four chars and use not in('EXT_','TMP_'

Or perhapos have a wee table with them in and do an outer join.

Another option maybe would be

Not Like '[ET][XM][TP]_%' not very readable that though.

PS presumably you are escaping underscore seeing as it's any single character.

Upvotes: 0

A.B.Cade
A.B.Cade

Reputation: 16915

maybe with regexp_like http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/conditions018.htm

something like this:

... where not regexp_like(object_name, 'EXT\.*|TMP\.*|.....')

Upvotes: 4

Related Questions