sqloraclesubstr
⑴ sql中級進階(三)hive的 collect_set 與oracle的wm_concat
Oracle的wm_concat用於多行合並為一行，原始版本使用逗號鏈接。例如：
（1）原始版本: 'aaa,bbb,ccc,ddd,eee'
修改版本使用'|'鏈接，例如：
（2）修改版本: 'aaa|bbb|ccc|ddd|eee'
（2）修改版本2: 'aaa|bbb|ccc|ddd|eee'，並去重。
Oracle的SQL使用REGEXP_SUBSTR函數來一行拆分成多行。其格式為：
REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
參數說明如下：
- __srcstr：需要進行正則處理的字元串
- __pattern：進行匹配的正則表達式
- __position：起始位置，從第幾個字元開始正則表達式匹配（默認為1）
- __occurrence：標識第幾個匹配組，默認為1
- __modifier：模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索。默認為'c'。）
Hive的collect_set函數用於去重地將同一分組的不同行數據聚合成一個集合。例如，創建原數據表：
CREATE TABLE stud (name string, area string, course string, score int);
向原數據表插入數據：
INSERT INTO TABLE stud VALUES('zhang3','bj','math',88);
查詢表中數據：
SELECT * FROM stud;
將同一分組的數據聚合成一個集合：
SELECT course, collect_set(area), avg(score) FROM stud GROUP BY course;
使用下標可以取某一個集合元素：
SELECT course, collect_set(area)[0], avg(score) FROM stud GROUP BY course;
通常，此過程的最終寫法可以使用concat_ws函數連接集合中的元素，例如：
concat_ws(',', collect_set(area))
輸出結果為：sh,bj
連接符號也可以改為其他字元，例如'|'。
總結：Oracle和Hive中wm_concat與collect_set、collect_list函數的主要功能相似，都是用於數據聚合處理。它們的不同在於語法和應用場景。這些函數在數據處理和分析中廣泛使用，提供了有效的方法來管理多行數據。
⑵ sql資料庫截取字元串函數
SQL截取字元串函數：
A.截取從字元串左邊開始N個字元
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.xrss.cn'
Select Left(@S1,4)
------------------------------------
顯示結果: http
B.截取從字元串右邊開始N個字元(例如取字元www.163.com)
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.163.com'
Select right(@S1,11)
------------------------------------
顯示結果: www.163.com
C.截取字元串中任意位置及長度(例如取字元www)
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.xrss.cn'
Select SUBSTRING(@S1,8,3)
------------------------------------
顯示結果: www
以上例子皆是已知截取位置及長度,下面介紹未知位置的例子
2.截取未知位置的函數
A.截取指定字元串後的字元串(例如截取http://後面的字元串)
方法一:
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.xrss.cn'
Select Substring(@S1,CHARINDEX('www',@S1)+1,Len(@S1))
------------------------------------
顯示結果: www.163.com
需要注意:CHARINDEX函數搜索字元串時,不區分大小寫,因此CHARINDEX('www',@S1)也可以寫成CHARINDEX('WWW',@S1)
方法二:(與方法一類似)
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.xrss.cn'
Select Substring(@S1,PATINDEX('%www%',@S1)+1,Len(@S1))
--此處也可以這樣寫:Select
Substring(@S1,PATINDEX('%//%',@S1)+2,Len(@S1))
------------------------------------
顯示結果: www.163.com
函數PATINDEX與CHARINDEX區別在於:前者可以參數一些參數,增加查詢的功能
方法三:
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.xrss.cn'
Select REPLACE(@S1,'http://','')
------------------------------------
顯示結果: www.163.com
利用字元替換函數REPLACE,將除需要顯示字元串外的字元替換為空
方法四:
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='http://www.xrss.cn'
Select
STUFF(@S1,CHARINDEX('http://',@S1),Len('http://'),'')
------------------------------------
顯示結果: www.163.com
函數STUFF與REPLACE區別在於:前者可以指定替換范圍,而後者則是全部范圍內替換
B.截取指定字元後的字元串(例如截取C:\Windows\test.txt中文件名)
與A不同的是,當搜索對象不是一個時,利用上面的方法只能搜索到第一個位置
方法一:
以下是代碼片段：
Declare @S1 varchar(100)
Select @S1='C:\Windows\test.txt'
select right(@S1,charindex('\',REVERSE(@S1))-1)
-------------------------------------
顯示結果: text.txt
利用函數REVERSE獲取需要截取的字元串長度