Need SQL help -

S567

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

 )

 

user10378862

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

ShankarS-Oracle

Good Luck.

user10378862

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