Script: PostgreSQL 实现字符串拆分 split (二)

几年前写过一篇在oracle 使用oracle函数实现的类似java 语言中的split 拆分功能，这篇改用PostgreSQL实现类型的功能。

Script:oracle拆分逗号分隔字符串 实现split

在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 (
postgres(# select regexp_split_to_array('first field, second field , third field',E'\\,') as  ar_str
postgres(# ) v;
    str1     |      str2      |     str3
-------------+----------------+--------------
 first field |  second field  |  third field
(1 row)

postgres=# select ar_str[_idx] from (
postgres(#   select  generate_subscripts(ar_str,1) as _idx, ar_str from (
postgres(#   select regexp_split_to_array('first field, second field , third field',E'\\,') as  ar_str
postgres(#   ) v
postgres(# ) v1;
     ar_str
----------------
 first field
  second field
  third field


postgres=# SELECT (regexp_split_to_array('www.ANBOB.com', E'\\.'))[2];
 regexp_split_to_array
-----------------------
 ANBOB
(1 row)

-- 以空格拆分
postgres=# SELECT regexp_split_to_array('hello PG', E'\\s+')
postgres-# ;
 regexp_split_to_array
-----------------------
 {hello,PG}
(1 row)

— regexp_split_to_table

postgres=# select regexp_split_to_table('first field, second field , third field',',') as tab_str;
    tab_str
----------------
 first field
  second field
  third field
(3 rows)
postgres=# SELECT tab_num::INTEGER
postgres-# FROM regexp_split_to_table('1,3,2,4,7,5,9,10', ',') AS tab_num
postgres-# ORDER BY 1;
tab_num
---------
1
2
3
4
5
7
9
10
(8 rows)

WOW! 是否很方便?