Home » SQL & PL/SQL » SQL & PL/SQL » Split Data in Column & Split Data in Excel (merged) (Excel 2016)
|
|
|
Split Data in Column [message #671063 is a reply to message #671060] |
Mon, 13 August 2018 13:26 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi All,
Created a table and loaded data.Now I want to split the data in Column "Consortia" for the same Site_id
CREATE TABLE TEST_DATA
(SITE_ID VARCHAR2(2000)
,CONSORTIA VARCHAR2(2000));
I tried to write below but the data in the "Column-CONSORTIA" is not Split
Please help.
WITH
CONSORTIA as (
SELECT
to_char(regexp_substr(CONSORTIA, '[^,]+', 1, column_value)) CONSORTIA
from (select * from TEST_DATA),
table(cast(multiset(select level from dual
connect by level <= regexp_count(CONSORTIA,',')+1)
as sys.odciNumberList))
where rownum > 0
)
SELECT * FROM TEST_DATA
/
Uploaded the excel file that is loaded in to the table.
|
|
|
Re: Split Data in Column [message #671064 is a reply to message #671063] |
Mon, 13 August 2018 13:34 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
vharish006 wrote on Mon, 13 August 2018 11:26Hi All,
Created a table and loaded data.Now I want to split the data in Column "Consortia" for the same Site_id
CREATE TABLE TEST_DATA
(SITE_ID VARCHAR2(2000)
,CONSORTIA VARCHAR2(2000));
I tried to write below but the data in the "Column-CONSORTIA" is not Split
Please help.
WITH
CONSORTIA as (
SELECT
to_char(regexp_substr(CONSORTIA, '[^,]+', 1, column_value)) CONSORTIA
from (select * from TEST_DATA),
table(cast(multiset(select level from dual
connect by level <= regexp_count(CONSORTIA,',')+1)
as sys.odciNumberList))
where rownum > 0
)
SELECT * FROM TEST_DATA
/
Uploaded the excel file that is loaded in to the table.
Please provide needed INSERT statements.
Please provide expected & desired results.
|
|
|
Re: Split Data in Excel [message #671065 is a reply to message #671062] |
Mon, 13 August 2018 13:42 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can do it using Oracle (but is this necessary?) spooling something like:
SQL> CREATE TABLE t_ext (
2 site_id VARCHAR2(30),
3 consortia VARCHAR2(4000)
4 )
5 ORGANIZATION EXTERNAL (
6 TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY my_dir
8 ACCESS PARAMETERS (
9 RECORDS DELIMITED BY NEWLINE
10 SKIP 1
11 NOBADFILE
12 NOLOGFILE
13 NODISCARDFILE
14 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
15 MISSING FIELD VALUES ARE NULL
16 (site_id, consortia)
17 )
18 LOCATION ('Book2.csv')
19 )
20 /
Table created.
SQL> col consortia format a80 word_wrap
SQL> SELECT * FROM t_ext WHERE rownum <= 3
2 /
SITE_ID CONSORTIA
------------------------------ --------------------------------------------------------------------------------
nci TAFE NSW Library
umac Inter Regional Consortium (IRC) P3-G2 (Primary), Hong Kong & Macau
Consortium - 2014, Inter Regional Consortium (IRC) P2-G2
lnhk The Joint University Librarians Advisory Committee (JULAC) (Primary), The Joint
University Librarians Advisory Committee (JULAC) II, Hong Kong & Macau
Consortium - 2014, Inter Regional Consortium (IRC) P3-G2
3 rows selected.
SQL> SELECT site_id || ',"' || ltrim(regexp_substr(consortia, '[^,]+', 1, column_value)) || '"' res
2 FROM (SELECT * FROM t_ext WHERE ROWNUM <= 3),
3 table(cast(multiset(select level word from dual
4 connect by level <= regexp_count(consortia,',')+1
5 ) as sys.odcinumberlist))
6 /
RES
---------------------------------------------------------------------------------------------------------------
nci,"TAFE NSW Library"
umac,"Inter Regional Consortium (IRC) P3-G2 (Primary)"
umac,"Hong Kong & Macau Consortium - 2014"
umac,"Inter Regional Consortium (IRC) P2-G2"
lnhk,"The Joint University Librarians Advisory Committee (JULAC) (Primary)"
lnhk,"The Joint University Librarians Advisory Committee (JULAC) II"
lnhk,"Hong Kong & Macau Consortium - 2014"
lnhk,"Inter Regional Consortium (IRC) P3-G2"
8 rows selected.
[Updated on: Mon, 13 August 2018 13:54] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:40:59 CDT 2024
|