Search code examples
sqlregexoracleplsqlregexp-substr

Select a word from an Oracle query string using regular expression

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 ?

Solution

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

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