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 | kenyon2 | just | china3 | iam | lovingU4 | test | 5 | | adele (5 rows)postgres=# select concat(id,name,remark) from t_kenyon;concat -------------1testkenyon2justchina3iamlovingU4test5adele (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,kenyon2,just,china3,iam,lovingU4,test5,adele (5 rows)postgres=# select concat_ws('_',id,name,remark) from t_kenyon;concat_ws ---------------1_test_kenyon2_just_china3_iam_lovingU4_test5_adele (5 rows)postgres=# select concat_ws('',id,name,remark) from t_kenyon;concat_ws -------------1testkenyon2justchina3iamlovingU4test5adele (5 rows)postgres=# select concat_ws('^_*',id,name,remark) from t_kenyon;concat_ws -------------------1^_*test^_*kenyon2^_*just^_*china3^_*iam^_*lovingU4^_*test5^_*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 -----------------------kenyonlovechina! (5 rows)--按分割符切割 postgres=# SELECT regexp_split_to_table('kenyon, china loves',E'\\s');regexp_split_to_table -----------------------kenyon,chinaloves (3 rows)--按字母切割 postgres=# SELECT regexp_split_to_table('kenyon,,china',E'\\s*');regexp_split_to_table -----------------------kenyon,,china (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'表示转义空格
转自：https://my.oschina.net/Kenyon/blog/76819