Thursday 4 March 2010

Escaping wildcard searches

What if the character you want to search for is one of Oracle's wildcards?

Today I wanted a list of all Apex schema related synonyms, but I wanted to excluded any APEXLIB stuff.
-- Find actual object names for apex objects
select synonym_name, table_name
from dba_synonyms
where synonym_name like 'APEX/_%' escape '/'
and table_owner like 'APEX/_%' escape '/'
and owner like 'APEX/_%' escape '/'
order by synonym_name;

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
APEX_APPLICATION               WWV_FLOW
APEX_APPLICATION_FILES         WWV_FLOW_FILES
APEX_APPLICATION_GLOBAL        WWV_FLOW_GLOBAL
APEX_COLLECTION                WWV_FLOW_COLLECTION
APEX_COLLECTIONS               WWV_FLOW_COLLECTIONS
APEX_CUSTOM_AUTH               HTMLDB_CUSTOM_AUTH
APEX_INSTANCE_ADMIN            WWV_FLOW_INSTANCE_ADMIN
APEX_UTIL                      HTMLDB_UTIL
...
I know in times passed I've also wanted to search for all database objects that contain _ROLE_

select * from all_objects where object_name like '%\_ROLE\_%' escape '\';

Obviously it need not be a forward or back slash. If you don't want to confuse yourself when you also have '\' characters in your search term. A tilde is a favourite of mine.

select directory_path from all_directories where directory_path like '%\sales~_%' escape '~';

DIRECTORY_PATH
------------------------------------------
E:\oracle\sw10g\demo\schema\sales_history\


In fact, if you don't succeed the escape character with Oracle's wildcards (% or _), then you'll receive the following error:
ORA-01424: missing or illegal character following the escape character

Documentation on this feature can be found here.

No comments: