1、regexp_replace
select regexp_replace("BZ-BZBSC1.413.1-pn237-fr283~@#$%","\\~|\\!|\\@|\\#|\\$|\\%|\\^|\\&|\\*|\\(|\\)",'') as oid;
+------------------------------+
| oid |
+------------------------------+
| BZ-BZBSC1.413.1-pn237-fr283 |
+------------------------------+
功能是去掉 “~!@#¥%……&*()“
2、locate
实例:
select locate(':',"0:283$2:242");
+------+
| _c0 |
+------+
| 2 |
+------+
功能是取得从左至右第一个":"的位置
3、substr
select substr("0:283$2:242",3);
+------------+
| _c0 |
+------------+
| 283$2:242 |
+------------+
substr("0:283$2:242",3) 截取从第三位开始(包括第三位)往右的所有字符串
select substr("0:283$2:242",3,4);
+-------+
| _c0 |
+-------+
| 283$ |
+-------+
substr("0:283$2:242",3,4) 截取从第三位开始(包括第三位)往右再截取四个字符串
4、explode(an)
an是数组
行转列,一行对应数组中的一个元素
select explode(split(regexp_replace(substr("0:283$2:242",locate(':',"0:283$2:242")+1),"\\$._*:",':'),':')) as freq;
+-------+
| freq |
+-------+
| 283 |
| 242 |
+-------+