Oracle divides a column into multiple columns

Oracle divides a column into multiple columns

In Oracle, we can use Listagg (Oracle 11g and above) to merge one column of multiple rows into one column, and now our requirement is to split one column into multiple columns.

First create a table and insert the 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 with the following structure:

NAME
Stephen King
Tom Hanks
Christopher Nolan

Now split the table into two columns:

SELECT   REGEXP_SUBSTR(name,‘^[^ ]*‘)  first_name,  ‘([[:alpha:]]+)$‘)   middle_nameFROM CELEBRITY

Where we use the REGEXP_SUBSTR function, which uses regular matching to divide 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 to split:

SELECT     1INSTR‘ ‘)-1) FIRST_NAME,    INSTR‘ ‘)+1) MIDDLE_NAMEFROM  CELEBRITY

Or we do:

SELECT  1, SPACE_POS-1) FIRST_NAME,  SUBSTR(NAME, SPACE_POS+1) MIDDLE_NAMEFROM (SELECT NAME,INSTR‘ ‘) SPACE_POSFROM CELEBRITY)

Each of these methods can be divided into two columns.

Oracle divides a column into multiple columns