| Split column to rows in Oracle Sql [message #640128] |
Wed, 22 July 2015 17:03  |
 |
vspn
Messages: 10
Registered: July 2015
|
Junior Member |
|
|
Hi
I have a column which has values separated by comma(,). I need to split this into multiple rows.
SELECT NOTE FROM WMS.ORDER_NOTE OR_NT, WMS.ORDERS
WHERE OR_NT.ORDER_ID = ORDERS.ORDER_ID
AND OR_NT.Note_Type = 'CU'
AND OR_NT.Note_Code = 'SM'
AND ORDERS.TC_ORDER_ID = '9701187158';
Note
---------
2252998,2252999,2253000
I need to split the above Note into 3 rows as below.
Note
------
2252998
2252999
2253000
Please help with the SQL query
|
|
|
|
|
|
|
|
|
|
| Re: Split column to rows in Oracle Sql [message #640137 is a reply to message #640132] |
Wed, 22 July 2015 23:56  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jgjeetu wrote on Thu, 23 July 2015 10:13
for 10g & below versions use:-
select replace(columnname,',',chr(10)) from table;
How would that generate 3 rows? OP asked to split into rows:
Quote:I need to split the above Note into 3 rows as below.
SQL> WITH DATA AS(
2 SELECT '2252998,2252999,2253000' str FROM dual
3 )
4 SELECT count(*) FROM(
5 select replace(str,',',chr(10)) from data);
COUNT(*)
----------
1
SQL>
You are just replacing comma with Line feed and not generating rows.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
