Forum Stats

  • 3,727,353 Users
  • 2,245,374 Discussions
  • 7,852,752 Comments

Discussions

Need SQL help -

S567
S567 Member Posts: 345 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

 )

 

Answers

  • ShankarS-Oracle
    ShankarS-Oracle Member Posts: 224 Employee
  • user10378862
    user10378862 Member Posts: 0 Green Ribbon

    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


Sign In or Register to comment.