Select a word from an Oracle query string using regular expression

Question

I have to find a table name from a query dynamically. The table name in the query may or may not be preceded by the schema name. But the table name will always be followed by a "@dblink" string. The query pattern is like : 'select c1, c2 from schema.table_name@dblink ...' Or, it could be like : 'select c1,c2,c3 from table_name@dblink ...' That is, the number of columns - c1, c2 etc - are variable. Also, the developers can probably put in multiple consecutive spaces anywhere between any 2 words.

My objective is to retrieve the name of the table "table_name".

How can I do this using regular expression ?

Answer 1

If you want the word that preceds '@dblink', you can do:

regexp_replace(mycol, '.*\W(\w+)@dblink.*', '\1')

Answer 2

declare var

SELECT name into var  from table where name LIKE '%@dblink';

You can print like this

DBMS_OUTPUT.PUT_LINE (var);