PostgreSQL数据库切割和组合字段函数

Postgresql里面内置了很多的实用函数，下面介绍下组合和切割函数
环境：PostgreSQL 9.1.2
          CENTOS 5.7 final

一.组合函数
1.concat
a.介绍

concat(str "any" [, str "any" [, ...]])    Concatenate all but first arguments with separators.  The first parameter is used as a separator.   NULL arguments are ignored.

b.实际例子:

postgres=# create table t_kenyon(id int,name varchar(10),remark text);  CREATE TABLE  postgres=# insert into t_kenyon values(1,'test','kenyon'),(2,'just','china'),(3,'iam','lovingU');  INSERT 0 3  postgres=# insert into t_kenyon values(4,'test',null);  INSERT 0 1  postgres=# insert into t_kenyon values(5,null,'adele');  INSERT 0 1  postgres=# select * from t_kenyon;   id | name | remark    ----+------+---------    1 | test | kenyon    2 | just | china    3 | iam  | lovingU    4 | test |     5 |      | adele  (5 rows)    postgres=# select concat(id,name,remark) from t_kenyon;     concat      -------------   1testkenyon   2justchina   3iamlovingU   4test   5adele  (5 rows)

c.说明 concat函数纯粹是一个拼接函数，可以忽略null值拼接，拼接的值没有分隔符，如果需要分割符，则需要用下面的函数concat_ws。

2.concat_ws
a.介绍

concat_ws(sep text, str "any" [, str "any" [,...] ])    Concatenate all but first arguments with separators.  The first parameter is used as a separator.  NULL arguments are ignored.

b.实际应用

postgres=# select concat_ws(',',id,name,remark) from t_kenyon;     concat_ws     ---------------   1,test,kenyon   2,just,china   3,iam,lovingU   4,test   5,adele  (5 rows)    postgres=# select concat_ws('_',id,name,remark) from t_kenyon;     concat_ws     ---------------   1_test_kenyon   2_just_china   3_iam_lovingU   4_test   5_adele  (5 rows)    postgres=# select concat_ws('',id,name,remark) from t_kenyon;    concat_ws    -------------   1testkenyon   2justchina   3iamlovingU   4test   5adele  (5 rows)    postgres=# select concat_ws('^_*',id,name,remark) from t_kenyon;       concat_ws       -------------------   1^_*test^_*kenyon   2^_*just^_*china   3^_*iam^_*lovingU   4^_*test   5^_*adele  (5 rows)

c.说明 concat_ws函数比concat函数多了分隔符的功能，其实就是concat的升级版，假如分隔符为'',则取出来的结果和concat是一样的。其功能与mysql中的group_concat函数比较类似，但也有不同，pg中concat_ws分隔符还支持多个字符作为分隔符的，日常用得更多的可能是||。 

二、切割函数
1.split_part
a.介绍

split_part(string text, delimiter text, field int)    Split string on delimiter and return the given field (counting from one)

b.实际例子

postgres=# select split_part('abc~@~def~@~ghi','~@~', 2);   split_part   ------------   def  (1 row)    postgres=# select split_part('now|year|month','|',3);   split_part   ------------   month  (1 row)

c.说明 该函数对按分隔符去取某个特定位置上的值非常有效果

2.regexp_split_to_table
a.介绍

regexp_split_to_table(string text, pattern text [, flags text])    Split string using a POSIX regular expression as the delimiter.

b.使用例子

postgres=# SELECT regexp_split_to_table('kenyon,love,,china,!',',');   regexp_split_to_table   -----------------------   kenyon   love      china   !  (5 rows)    --按分割符切割  postgres=# SELECT regexp_split_to_table('kenyon,china,loves',',');   regexp_split_to_table   -----------------------   kenyon   china   loves  (3 rows)    --按字母切割  postgres=# SELECT regexp_split_to_table('kenyon,,china',E'\\s*');   regexp_split_to_table   -----------------------   k   e   n   y   o   n   ,   ,   c   h   i   n   a  (13 rows)

3.regexp_split_to_array
a.介绍

regexp_split_to_array(string text, pattern text [, flags text ])    Split string using a POSIX regular expression as the delimiter.

b.实际例子

postgres=# SELECT regexp_split_to_array('kenyon,love,,china,!',',');    regexp_split_to_array     --------------------------   {kenyon,love,"",china,!}  (1 row)    postgres=# SELECT regexp_split_to_array('kenyon,love,,china!','s*');               regexp_split_to_array               -----------------------------------------------   {k,e,n,y,o,n,",",l,o,v,e,",",",",c,h,i,n,a,!}  (1 row)

c.说明
上面用到的flag里的s*表示split all