Oracle SQL 劈開字串

標籤：

一、資料範例

二、劈開單行

SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, rownum) h2  FROM  (select id_,name_ from test_reg_count t  where t.id_ =2 )CONNECT BY level <= length(name_)-length(replace(name_,‘,‘,‘‘))+1;--或者SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, rownum) h2  FROM  (select id_,name_ from test_reg_count t  where t.id_ =2 )CONNECT BY REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, rownum) is not null;

三、劈開多行

SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, level) name_  FROM  test_reg_count   CONNECT BY level <= length(name_)-length(replace(name_,‘,‘,‘‘))+1                AND name_ = PRIOR name_                 AND PRIOR dbms_random.value <1;       --或者SELECT id_,REGEXP_SUBSTR(name_, ‘[^,]+‘, 1, level) name_  FROM  test_reg_count   CONNECT BY level <= length(name_)-length(replace(name_,‘,‘,‘‘))+1                AND name_ = PRIOR name_                 AND PRIOR SYS_GUID() is not null;

這裡最重要的是 prior操作符，關於prior的解釋Oracle的官方文檔是這麼描述的：

　　In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

　　PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

Oracle SQL 劈開字串