目前Trafodion已经支持split_part函数,其函数定义与用法和PostGreSQL中的split_part相同。具体语法定义如下(摘自PG文档,由于Trafodion的SQL手册尚未添加),
函数 | 返回类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
split_part(string text, delimiter text, field int) | text | Split string on delimiter and return the given field (counting from one) | split_part(‘abc@def@ghi’, ‘@’, 2) | def |
split_part函数可以根据指定字符来截取字符串中某一段的位置,相比于substr+instr的方式来截取要简便很多。下面是在Trafodion尝试的几种split_part示例,供参考,
//按单个字符匹配(英文)
SQL>select split_part('a%b%c','%',3) from dual;
(EXPR)
-----------
c
--- 1 row(s) selected.
//未匹配到的情况
SQL>select split_part('a%b%c','%',4) from dual;
(EXPR)
-----------
--- 1 row(s) selected.
//传入负值不支持
SQL>select split_part('a%b%c','%',-1) from dual;
*** ERROR[8691] Field position must be greater than zero, currently is -1. [2019-01-04 11:22:37]
//按单个字符匹配(中文)
SQL>select split_part('我@你@他','@',2) from dual;
(EXPR)
--------------
你
--- 1 row(s) selected.
//按多个字符匹配(中文)
SQL>select split_part('我:@你:@他',':@',1) from dual;
(EXPR)
--------------
我
--- 1 row(s) selected.