2

I would require your help to retrieve the selected columns from the oracle query like

SELECT col1,col2,col3+col4 as col3_4_sum,col5*col6 as col5_6_mul from tab1;

I want to retrieve the below output as 

Details
==========
Col1
Col2
Col3+Col4
Col5*Col6

Not the alias ,because this can be easily retrieved from dbms_sql.describe_columns oracle utility ,Can someone suggest some data dictionaries to retrieve this

EDIT :Different forms,but i need a final output column only 

Query 2:SELECT col1,col2,col3+col4 as col3_4_sum,col5*col6 as col5_6_mul
 FROM (SELECT col1,col2,col3,col4,col5,col6 FROM tab1)

Query3:WITH tab as (SELECT col1,col2,col3,col4,col5,col6 FROM tab1)
SELECT col1,col2,col3+col4 as col3_4_sum,col5*col6 as col5_6_mul
     FROM tab;
Stay Curious
  • 101
  • 10
  • Look at the REPLACE() function. This is the easiest way - replace comma with chr(10) - new line, and you will get your output exactly. There are other ways with REGEXP, etc... It all depends on your requirements I guess. – Art Apr 17 '18 at 15:12
  • @Art:There are many scenerio's ,like the With clause , Inline view in the query REGEX will be complex – Stay Curious Apr 18 '18 at 07:43
  • And what is your question than? What is your exact requirement? There are a lot of examples here and online in general how to convert comma to tabe or row to table. I suggested the easiest way. The same can be done with REGEXP. The assumption in both cases that your data is always separated by comma. – Art Apr 18 '18 at 18:31
  • @Art: The question is simple ,I have a query ,which can be written in different forms like a query can have be written with "With" clause ,a query can have multiple inline views ,but i want to fetch the selected column of the final output thats it ,how you can achieve this by simple regex ,can you please explain , because i am not seeing it so simple , i ll update the question with different forms – Stay Curious Apr 19 '18 at 05:06
  • good resource- https://www.techonthenet.com/oracle/functions/regexp_substr.php – Art Apr 19 '18 at 20:10

1 Answers1

-1

I hope this is what you looking for.

SELECT trim(regexp_substr('col1,col2,col3+col4,col5*col6', '[^,]+', 1, LEVEL)) str_2_tab
 FROM dual
CONNECT BY LEVEL <= regexp_count('col1,col2,col3+col4,col5*col6', ',')+1
/

But the simplest way is using chr(10) to replace the commas: 

SELECT REPLACE('col1,col2,col3+col4,col5*col6', ',', chr(10)) str_2_tab FROM dual

Output is the same:

STRING_2_TAB
------------
col1
col2
col3+col4
col5*col6

Good resources to check for Parameters and Arguments: https://www.techonthenet.com/oracle/functions/regexp_substr.php https://oracle-base.com/articles/misc/regular-expressions-support-in-oracle#example1

Art
  • 5,616
  • 1
  • 20
  • 22
  • Thanks for the solution ,I have expertise in regex ,but my original question remains the same ,how can you retrieve only the output column from a query ,not the inline view ,please try your solution with query 2 and 3 and the query can be complicated – Stay Curious Apr 23 '18 at 11:38
  • @Stay Curious - I think you do not know what you want and cannot even explain it. I used your query you post in your question. What my query has to do with inline view,? You need to be able to at least explain your problem clear. And at least I tried to understand you but others simply ignored you, which was the right thing to do. – Art Apr 25 '18 at 12:53
  • I am trying to explain things and neither I have downvoted you,I just want some way in database to find the final column expressions in the output as mentioned in the question above, but the query itself can be very complex ,the example i have mentioned above ,there can be multiple select in the same SQL statement ,then how your regular expression will find which one to pick ,that's why i have asked for solution which is totally based on the Oracle utility or data dictionaries – Stay Curious May 07 '18 at 17:08
  • @Stay Curious - votes and scores do not matter to me, honestly. I'm here to learn. But it is hard to understand what do you want. – Art May 07 '18 at 19:10