How to Efficiently Split Comma-Separated Values into Columns in Oracle?

Oracle: Efficiently Parsing Comma-Separated Values into Columns

Data processing often requires splitting comma-separated values (CSV) into individual columns. Oracle offers an efficient solution using regular expressions, particularly beneficial when dealing with a large number of values (up to 255 in this case). This avoids the cumbersome repetition of SUBSTR() functions for each column.

The Challenge: Extracting up to 255 comma-separated values into distinct columns.

The Solution: Leveraging REGEXP_SUBSTR()

The key is the REGEXP_SUBSTR() function and the following regular expression:

REGEXP_SUBSTR(CSV_VALUES, '(.*?)(,|$)', 1, COLUMN_NUMBER, NULL, 1)

Understanding the Regex:

• (.*?): Matches any character (.) zero or more times (*), non-greedily (?). This ensures it captures only up to the next comma.
• (,|$): Matches either a comma (,) or the end of the string ($). This delimits the values.
• 1: Specifies that the first captured group (the value itself, excluding the comma) should be returned.
• COLUMN_NUMBER: Indicates which value (column) to extract (1 for the first, 2 for the second, and so on).
• NULL, 1: These parameters are used for case-insensitive matching and selecting the first occurrence.

Illustrative Example:

Consider this CSV string:

<code>CSV_VALUES: 1.25, 3.87, 2, 5, 4, 3.3</code>

To get the first value (1.25):

REGEXP_SUBSTR(CSV_VALUES, '(.*?)(,|$)', 1, 1, NULL, 1)

For the second value (3.87):

REGEXP_SUBSTR(CSV_VALUES, '(.*?)(,|$)', 1, 2, NULL, 1)

By iterating through COLUMN_NUMBER, all values are extracted into individual columns. This method scales efficiently regardless of the number of comma-separated values within the string.

The above is the detailed content of How to Efficiently Split Comma-Separated Values into Columns in Oracle?. For more information, please follow other related articles on the PHP Chinese website!