一、Oracle數據庫按分隔符拆分字元串
1、應用函數
REGExp_SUBSTR
2、語法
REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
3、參數解釋
__srcstr :需要進行正則處理的字元串
__pattern :進行匹配的正則表達式
__position :起始位置,從第幾個字元開始正則表達式匹配(默認爲1)
__occurrence :標識第幾個匹配組,默認爲1
__modifier :模式('i'不區分大小寫進行檢索;'c'區分大小寫進行檢索。默認爲'c'。)
4、測試SQL
select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
執行結果:
1select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
5、REGEXP_SUBSTR+CONNECT BY 使用
按分隔符拆分字元串+CONNECT BY 動態參數
測試SQL:
SELECT
REGEXP_SUBSTR ('1,2,3,4,5', '[^,]+', 1,ROWNUM)
FROM dual
CONNECT BY ROWNUM <= LENGTH( '1,2,3,4,5' ) - LENGTH(regexp_replace('1,2,3,4,5', ',', '' )) + 1
二、hive數據庫按分隔符拆分字元串
1,split()函數
定義
split()函數是用於切分數據,也就是將一串字元串切割成了一個數組
語法
語法:split(string str, string pat)
返回值:數組類型array
參數解釋
string str :待分割字元串
string pat:分割符
測試SQL:
select split ('wo,shi,xiao,ming',',');
執行結果:
["wo","shi","xiao","ming"]
2、explode函數
定義
explode()函數是用於打散行的函數,將一行的數據拆分成一列
語法
explode(array/map類型)
select explode(array_col) as new_col from table_name
測試SQL:
select explode(array("wo","shi","xiao","ming")) as word;
執行結果:
wo
shi
xiao
ming
explode函數的侷限性
不能關聯原有的表中的其他字段
不能與group by、cluster by、distribute by、sort by聯用
不能進行UDTF嵌套
參數只能是兩種類型
一個select後面只能獲得一個explode產生的視圖,如果要顯示多個列,則需要將多個視圖合併。
3、lateral view
定義:
Lateral View用於和UDTF函數(explode、split)結合來使用
首先透過UDTF函數拆分成多行,再將多行結果組合成一個支援別名的虛擬表。虛擬表相當於再和主表關聯, 從而達到添加“UDTF生成的字段“以外字段的目的, 即主表裏的字段或者主表運算後的字段。
主要解決在select使用UDTF做查詢過程中,查詢只能包含單個UDTF,不能包含其他字段、以及多個UDTF的問題
語法:
lateral view UDTF(expression) table_view as new_column;
參數解釋:
UDTF(expression):複合邏輯規則的UDTF函數,最常用的explode
table_view : 對應的虛擬表的表名
new_col: 虛擬表裏存放的有效字段
測試:
select col_type -- 已拆分數據
from table_name
lateral view explode(split(col,',')) t as col_type --col 爲需要拆分的字段