Forum Stats

  • 3,826,348 Users
  • 2,260,631 Discussions


Need SQL help -

S567 Member Posts: 434 Blue Ribbon

Hi Team,

I am using Oracle 11g.

I have a column in a table where string values are stored as SELECT stmnt-

I have a requirement to extract only the table names used in the select statements ( including schema tabes too) basically to pull out the string between FROM and WHERE.

with tbl as (

    select 'select * from cc where customer_id in (123)'  from dual union all

   select 'select * from mbs.email_address where customer_id in (123)' from dual union all

   select 'select * from kreddy.transactions where customer_id in (123)' from dual



Best Answer

  • user10378862
    user10378862 Member Posts: 0 Green Ribbon
    Answer ✓

    You could use substr and instr/regexp_instr functions in your query like for example

    select substr(query_column, regexp_instr(query_column, 'from ', 1, 1, 0, 'i') + 5, regexp_instr(query_column, ' where', 1, 1, 0, 'i') - (regexp_instr(query_column, 'from ', 1, 1, 0, 'i') + 5)) from tmp_query_tbl;

    this query is on a temporary table that has one column named query_column that stores queries