CODEWITHSUNDEEP
sqloracle-databaseoracle11gsubstring
DEVLOGIN
DEVLOGIN

Reputation: 87

Substring a SQL QUERY

I have a question on SQL I'm using oracle 11g, I want to know if it is possible to do this:

For example I have this SQL QUERY: 

SELECT DC.DASHBOARD_UID 
FROM   DASHBOARD_SAVE DS 
       LEFT JOIN DASHBOARD_COLOR DC 
           ON DC.DASHBOARD_UID = DS.DASHBOARD_UID

I want to know if it possible to make a substring of this query to get these informations:

COL_NAME: DC.DASHBOARD_UID TABLE_NAME: DASHBOARD_COLOR DC JUNCTURE_NAME: LEFT JOIN DASHBOARD_COLOR DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID

Thanks

Upvotes: 0

Views: 115

Answers (2)

Littlefoot
Littlefoot

Reputation: 143163

Well, in a simple case like yours, something like the following code might do the job.

However, in realistic situations, things can get much more complicated (e.g. inline views, WHERE clause, unions, ...) so you'd end up with writing your own parser. If that's what you want, go ahead. I had to do it when I was a student (not with SQL, though) and it was interesting.

Here you go (temp CTE is used to remove new line characters and multiple spaces):

SQL> with test (col) as
  2    (select 'SELECT DC.DASHBOARD_UID, DS.SOME_COL
  3             FROM   DASHBOARD_SAVE DS
  4             LEFT JOIN DASHBOARD_COLOR DC
  5               ON DC.DASHBOARD_UID = DS.DASHBOARD_UID'
  6             from dual
  7    ),
  8  temp as
  9    (select regexp_replace(UPPER(replace(col, chr(10), '')), '\s{2,}', ' ') col
 10     from test
 11    )
 12  select
 13    trim(substr(col, instr(col, 'SELECT') + 7,
 14                     instr(col, 'FROM') - instr(col, 'SELECT') - 7
 15               )) column_list,
 16    --
 17    trim(regexp_replace(substr(col, instr(col, 'FROM') + 4,
 18                                    instr(col, 'JOIN') - instr(col, 'FROM') - 4
 19                              ),
 20                        'LEFT|RIGHT|CROSS', '')) table_name,
 21    --
 22    regexp_substr(col, '(LEFT|RIGHT|CROSS JOIN) .+') juncture_name
 23  From temp;

COLUMN_LIST                   TABLE_NAME        JUNCTURE_NAME
----------------------------- ----------------- -------------------------------------------------------------------
DC.DASHBOARD_UID, DS.SOME_COL DASHBOARD_SAVE DS LEFT JOIN DASHBOARD_COLOR DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID

SQL>

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

I guess you are looking for something like SPOOL filename. Spooling writes to your respective file whatever gets printed on the sql console after spool command as I see as per your query above you dont want to get the data but design creation as o/p

Upvotes: 1

Related Questions