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是一樣的。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',E'\\s'); 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，E'\\s'表示轉義空格