Split Oracle data rows into multiple rows

Source: Internet
Author: User

Split Oracle data rows into multiple rows

In work and study, we often encounter a situation where one row is divided into multiple rows of data. Here, we will make a comparison.


Single Row splitting


If the table has only one row of data, you can directly use the connect by + Regular Expression Method on the original table. For example:

Select regexp_substr ('192. 444 ',' [^.] + ', 1, level) col

From dual

Connect by level <= regexp_count ('192. 100', '\.') + 1

Output result:

Multi-row splitting


If multiple rows of data in the data table need to be split, you can also use the connect + regular method on the original table:

Method 1,

With t

(Select '1970. 100' col

From dual

Union all

Select '1970. 100' col

From dual)

Select regexp_substr (col, '[^.] +', 1, level)

From t

Connect by level <= regexp_count (col, '\. \') + 1

And col = prior col

And prior dbms_random.value> 0

Result:

Method 2: Associate the original table with the largest row of the constructed value:

With t

(Select '1970. 100' col

From dual

Union all

Select '1970. 100' col

From dual)

Select regexp_substr (col, '[^.] +', 1, lv)

From t, (select level lv from dual connect by level <10) B

Where B. lv <= regexp_count (t. col, '\. \') + 1

This method is used to set the second dataset to a smaller value than the possible maximum value, and then associate the two datasets. When the large data volume is split, this value is properly set and the number of rows to be split is relatively consistent, efficiency is higher than method 1's direct connect.

Method 3: Use the table function:

With t

(Select '1970. 100' col

From dual

Union all

Select '1970. 100' col

From dual)

Select column_value

From t,

Table (cast (multiset

(Select regexp_substr (col, '[^.] +', 1, level) dd

From dual

Connect by level <= regexp_count (t. col, '\. \') + 1)

Sys. odcivarchar2list)

Result:

The output column name of this method is fixed, and column_value depends on the output of sys. odcivarchar2list. This method is more efficient than the second method for splitting large data volumes.

In addition, when splitting a large amount of data, exercise caution when using the regular expression method. You can use substr + instr to replace the regular expression.

If the efficiency of the above method is still unsatisfactory, you can consider using plsql blocks.

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.