Oracle divides a column into multiple columns, while oracle splits one column into multiple columns.

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.