For example, there is the following table named bk_test. the following data is inserted.
CREATE TABLE BK_TESK(id varchar2(10),s varchar2(20));
insert into BK_TESK values ('A','1,2,3');
insert into BK_TESK values ('B','4,5,6');
insert into BK_TESK values ('C','7,8');
The query data is shown in the figure
We need to split the comma-separated data in field S into multiple rows. The result is shown in the figure
The code to implement splitting a row of data into multiple rows is shown below
SELECT
id,
TRIM(REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL)) AS s
FROM
BK_TESK
CONNECT BY
PRIOR SYS_GUid() IS NOT NULL
AND LEVEL <= REGEXP_COUNT(s, ',') + 1
AND PRIOR id = id
ORDER BY
id, s;
1: The main purpose of the PRIOR id = id condition is to ensure that the hierarchical query maintains continuity in processing each id
- The PRIOR keyword is used to reference the value of the previous line.
- This condition makes each iteration use the same id value.
- It prevents queries from spanning different id values and ensures that all split values for each id are handled correctly.
- For example, when processing the value of 'a', this condition ensures that it does not jump to the value of 'b' until all values of 'a' have been processed.