CODEWITHSUNDEEP
oracle-databaseplsql
Leverage
Leverage

Reputation: 73

Using List of values against REGEXP or Like

This is my present code

SELECT
   OBJECT_NAME  FROM ALL_OBJECTS WHERE
    REGEXP_LIKE ( OBJECT_NAME,
                  '^table1|^table2|^table3|..'
                  )
    AND OBJECT_TYPE = 'TABLE'

I want to avoid writing too many text against Regexp , so i created table which has all the table_name within it.

so that i can do something like

SELECT
   OBJECT_NAME  FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE (SELECT NAME FROM TABLE_WITH_LIST)
 
    AND OBJECT_TYPE = 'TABLE'

Any way i can achieve this ?

Upvotes: 0

Views: 28

Answers (1)

Paul W
Paul W

Reputation: 11538

Yes, of course:

SELECT object_name
  FROM all_objects o,
       table_with_list x
 WHERE o.object_name LIKE x.name
   AND object_type = 'TABLE'

You will have to load table_with_list with patterns LIKE recognizes (with the % where you want it)

Upvotes: 1

Related Questions