Forum Stats

  • 3,752,069 Users
  • 2,250,454 Discussions
  • 7,867,707 Comments

Discussions

Need SQL help -

S567
S567 Member Posts: 405 Red 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
    Accepted 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


Answers