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.