Oracle divides a column into multiple columns, while oracle splits one column into multiple columns.
Oracle divides a column into multiple columns
In Oracle, we can use listparts (Oracle 11g and above) to merge multiple rows into one column. Now we need to split one column into multiple columns.
Create a table and insert test data:
CREATE TABLE CELEBRITY( NAME VARCHAR2(100) );INSERT INTO CELEBRITY VALUES ('Stephen King');INSERT INTO CELEBRITY VALUES ('Tom Hanks');INSERT INTO CELEBRITY VALUES ('Christopher Nolan');
Query the table in the following structure:
NAME |
Stephen King |
Tom Hanks |
Christopher Nolan |
Split the table into two columns:
SELECT REGEXP_SUBSTR(name,'^[^ ]*') first_name, REGEXP_SUBSTR(name, '([[:alpha:]]+)$') middle_nameFROM CELEBRITY
Here we use the REGEXP_SUBSTR function, which uses regular expressions for matching and divides a column into two columns. The result is as follows:
FIRST_NAME |
MIDDLE_NAME |
Stephen |
King |
Tom |
Hanks |
Christopher |
Nolan |
Of course, we can use the INSTR function for segmentation:
SELECT SUBSTR(NAME, 1, INSTR(NAME, ' ')-1) FIRST_NAME, SUBSTR(NAME, INSTR(NAME, ' ')+1) MIDDLE_NAMEFROM CELEBRITY
Or we can do this:
SELECT SUBSTR(NAME, 1, SPACE_POS-1) FIRST_NAME, SUBSTR(NAME, SPACE_POS+1) MIDDLE_NAMEFROM (SELECT NAME,INSTR(NAME, ' ') SPACE_POSFROM CELEBRITY)
All of the above methods can divide a column into two columns.