Home » Other » Marketplace » Column to Rows - Number Seprated By Any Character
Column to Rows - Number Seprated By Any Character [message #501222] Mon, 28 March 2011 00:02 Go to next message
nimishsoft
Messages: 10
Registered: March 2011
Location: Noida, India
Junior Member

My problem was to convert

'374627467(92876/5674)988276+87234687/8974232*3746-984027374[32742-3746236'

to

VALUE
------------
374627467
92876
5674
988276
87234687
8974232
3746
984027374
32742
3746236

please check this out, what I have tried
http://nimishgarg.blogspot.com/2011/03/column-to-rows-number-seprated-by-any.html
Re: Column to Rows - Number Seprated By Any Character [message #501232 is a reply to message #501222] Mon, 28 March 2011 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing more than one of the usual solutions.
This site is full of adverts, the pages are heavy and it does not add anything to the other ones.
In addition, OP already showed that he can steal others work.
Do not go to this site.
Do not promote such behaviour visiting the site.

Regards
Michel
Re: Column to Rows - Number Seprated By Any Character [message #501233 is a reply to message #501222] Mon, 28 March 2011 01:55 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Hai,

Try this Query. Hope it will be helpful to u.

SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, LEVEL ) + 1
                     , INSTR (txt, ',', 1, LEVEL+1) - INSTR (txt, ',', 1, LEVEL) -1 
                     ) 
            )
         AS token
FROM ( SELECT ','||
              (SELECT TRANSLATE('374627467(92876/5674)988276+87234687/8974232*3746-984027374[32742-3746236', 
                                '(+/-*[)', 
                                ',,,,,,,') outp
               FROM dual)||',' AS txt  
       FROM dual )
CONNECT BY LEVEL <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1


Regards,
Paules.

[Updated on: Mon, 28 March 2011 01:59] by Moderator

Report message to a moderator

Re: Column to Rows - Number Seprated By Any Character [message #501234 is a reply to message #501233] Mon, 28 March 2011 02:07 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said nothing new and more than what has been posted sooooo many times
in all Oracle forums including here.

Regards
Michel
Previous Topic: Oracle Apps/Revenue Recognition Consultant
Next Topic: Oracle Financials/Payroll Functional SC clearance
Goto Forum:
  


Current Time: Fri Mar 29 00:25:15 CDT 2024