几年前写过一篇在oracle 使用oracle函数实现的类似java 语言中的split 拆分功能,这篇改用PostgreSQL实现类型的功能。
在PG提供了两个函数用于拆份字符串:
regexp_split_to_array:它根据正则表达式拆分字符串并将其部分返回到数组中。
regexp_split_to_table:它根据正则表达式将字符串拆分为多个部分,并将其部分返回到表的行中。
下面还是以”,”逗号分隔拆分:
— REGEXP_SPLIT_TO_ARRAY
postgres=# select 'first field, second field , third field' as str
postgres-# ;
str
-----------------------------------------
first field, second field , third field
(1 row)
postgres=# select regexp_split_to_array('first field, second field , third field',E'\\,') as ar_str;
ar_str
-------------------------------------------------
{"first field"," second field "," third field"}
(1 row)
postgres=# select ar_str[1] AS str1, ar_str[2] AS str2,ar_str[3] AS str3 from (