Oracle一行拆分為多行方法實例

目錄一、背景二、預期結果1、表A2、去除無效的字符，如'['、']'等3、按逗號分割后結果三、實現sql四、sql分析1、REGEXP_SUBSTR 函數2、 REGEXP_COUNT 函數3、REPLACE 函數附：多行轉一行總結一、背景

拼接字符串根據某種規律拆分并轉化為多行，只要拆分的主鍵和數據即可

二、預期結果1、表A

2、去除無效的字符，如'['、']'等

3、按逗號分割后結果

三、實現sql

1、去除無效的字符，如'['、']'等

SELECT ID, replace(replace(replace(test /*替換的列*/, '[', ''), ']', ''), ' ', '') FROM table /*替換的表*/

2、按逗號分割后結果

法一：

SELECT distinct ID,REGEXP_SUBSTR(test1 /*替換拆分的列*/, '[^,]+', 1, LEVEL, 'i') as test2 FROM table /*替換表*/CONNECT BY LEVEL <= LENGTH(test1 /*替換拆分的列*/) - LENGTH(REPLACE(test1 /*替換拆分的列*/, ',', '')) + 1 order by ID

法二：

SELECT distinct ID,REGEXP_SUBSTR(test1 /*替換拆分的列*/, '[^,]+', 1, LEVEL, 'i') as test2 FROM table /*替換表*/CONNECT BY LEVEL <= (regexp_count(test1 /*替換拆分的列*/, ',') + 1) order by ID

法三：數據量大時優先選用，效率高

SELECT ID, substr(test1 /*替換拆分的列*/, instr(test1 /*替換拆分的列*/, ',', 1, levels.lvl) + 1, instr(test1 /*替換拆分的列*/, ',', 1, levels.lvl + 1) - (instr(test1 /*替換拆分的列*/, ',', 1, levels.lvl) + 1)) as test1 /*替換拆分的列*/ FROM (SELECT id, ',' || test1 /*替換拆分的列*/ || ',' AS test1 /*替換拆分的列*/, length(test1 /*替換拆分的列*/) - nvl(length(REPLACE(test1 /*替換拆分的列*/, ',')), 0) + 1 AS cnt FROM table /*替換表*/) a, (SELECT rownum AS lvl FROM (SELECT MAX(length(test1 /*替換拆分的列*/ || ',') - nvl(length(REPLACE(test1 /*替換拆分的列*/, ',')), 0)) max_len FROM table /*替換表*/)CONNECT BY LEVEL <= max_len) levels WHERE levels.lvl <= a.cnt order by ID四、sql分析1、REGEXP_SUBSTR 函數

Regexp_Substr(String,pattern,position,occurrence ,modifier )一共包含了五個參數：

String：操作的字符串；pattern：正則表達式匹配規則，匹配到則返回；position：開始匹配的位置，默認當然是1；occurrence：標識第幾個匹配組，默認為1 ；modifier：模式（‘i‘不區分大小寫進行檢索，‘c‘區分大小寫進行檢索。默認為‘c‘）。

說明：level oracle關鍵字,表示查詢深度,用來實現層級查詢

2、 REGEXP_COUNT 函數

REGEXP_COUNT ( source_char, pattern [, position [, match_param]])

REGEXP_COUNT 返回pattern 在source_char 串中出現的次數。如果未找到匹配，則函數返回0。position 變量告訴Oracle 在源串的什么位置開始搜索。在開始位置之后每出現一次模式，都會使計數結果增加1。

參數：

source_char：指定要搜索的字符串；pattern：指定要搜索的正則表達式；position：要在源字符串中開始搜索的位置，缺省值為1；match_param：用于指定控制模式匹配行為的值，缺省值為NULL。 3、REPLACE 函數

把字符串中的 old（舊字符串） 替換成 new(新字符串)，如果指定第三個參數max，則替換不超過 max 次

Replace(old_text,start_num,num_chars，new_text)

=replace（要替換的字符串，開始位置，替換個數，新的文本）

舉例：replace(test /*替換的列*/, '[', '')

附：多行轉一行//使用 distinct 去除重復項listagg(distinct xx,',') within group(order by sort)//最后別忘記group by總結

到此這篇關于Oracle一行拆分為多行的文章就介紹到這了,更多相關Oracle一行拆分多行內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！