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

拼接字符串根據(jù)某種規(guī)律拆分并轉(zhuǎn)化為多行，只要拆分的主鍵和數(shù)據(jù)即可

二、預(yù)期結(jié)果1、表A

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

3、按逗號分割后結(jié)果

三、實(shí)現(xiàn)sql

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

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

2、按逗號分割后結(jié)果

法一：

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

法三：數(shù)據(jù)量大時優(yōu)先選用，效率高

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 函數(shù)

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

String：操作的字符串；pattern：正則表達(dá)式匹配規(guī)則，匹配到則返回；position：開始匹配的位置，默認(rèn)當(dāng)然是1；occurrence：標(biāo)識第幾個匹配組，默認(rèn)為1 ；modifier：模式（‘i‘不區(qū)分大小寫進(jìn)行檢索，‘c‘區(qū)分大小寫進(jìn)行檢索。默認(rèn)為‘c‘）。

說明：level oracle關(guān)鍵字,表示查詢深度,用來實(shí)現(xiàn)層級查詢

2、 REGEXP_COUNT 函數(shù)

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

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

參數(shù)：

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

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

Replace(old_text,start_num,num_chars，new_text)

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

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

附：多行轉(zhuǎn)一行//使用 distinct 去除重復(fù)項(xiàng)listagg(distinct xx,',') within group(order by sort)//最后別忘記group by總結(jié)

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