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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.