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.</pre>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)</pre>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.</pre>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 concatws('',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 concatws('^*',id,name,remark) from t_kenyon;

 concat_ws     


1^*test^kenyon
 2^_just^*china
 3^iam^_lovingU
 4^*test
 5^*adele
(5 rows)</pre>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)</pre>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)</pre>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.</pre>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)</pre>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.</pre>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)</pre>c.說明 

上面用到的flag里的s*表示split all