Postgresql里面内置了很多的实用函数，下面介绍下组合和切割函数
环境：PostgreSQL 9.1.2
CENTOS 5.7 final
一.组合函数
1.concat
a.语法介绍
1concat(str "any" [, str "any" [, ...]]) 2 3Concatenate all but first arguments with separators. 4The first parameter is used as a separator. 5NULL arguments are ignored.
b.实际例子:
1postgres=# create table t_kenyon(id int,name varchar(10),remark text); 2CREATE TABLE 3postgres=# insert into t_kenyon values(1,'test','kenyon'),(2,'just','china'),(3,'iam','lovingU'); 4INSERT 0 3 5postgres=# insert into t_kenyon values(4,'test',null); 6INSERT 0 1 7postgres=# insert into t_kenyon values(5,null,'adele'); 8INSERT 0 1 9postgres=# select * from t_kenyon; 10 id | name | remark 11----+------+--------- 12 1 | test | kenyon 13 2 | just | china 14 3 | iam | lovingU 15 4 | test | 16 5 | | adele 17(5 rows) 18 19postgres=# select concat(id,name,remark) from t_kenyon; 20 concat 21------------- 22 1testkenyon 23 2justchina 24 3iamlovingU 25 4test 26 5adele 27(5 rows)
c.说明
concat函数纯粹是一个拼接函数，可以忽略null值拼接，拼接的值没有分隔符，如果需要分割符，则需要用下面的函数concat_ws。
2.concat_ws
a.语法介绍
1concat_ws(sep text, str "any" [, str "any" [,...] ]) 2 3Concatenate all but first arguments with separators. 4The first parameter is used as a separator. 5NULL arguments are ignored.
b.实际应用
1postgres=# select concat_ws(',',id,name,remark) from t_kenyon; 2 concat_ws 3--------------- 4 1,test,kenyon 5 2,just,china 6 3,iam,lovingU 7 4,test 8 5,adele 9(5 rows) 10 11postgres=# select concat_ws('_',id,name,remark) from t_kenyon; 12 concat_ws 13--------------- 14 1_test_kenyon 15 2_just_china 16 3_iam_lovingU 17 4_test 18 5_adele 19(5 rows) 20 21postgres=# select concat_ws('',id,name,remark) from t_kenyon; 22 concat_ws 23------------- 24 1testkenyon 25 2justchina 26 3iamlovingU 27 4test 28 5adele 29(5 rows) 30 31postgres=# select concat_ws('^_*',id,name,remark) from t_kenyon; 32 concat_ws 33------------------- 34 1^_*test^_*kenyon 35 2^_*just^_*china 36 3^_*iam^_*lovingU 37 4^_*test 38 5^_*adele 39(5 rows)
c.说明 concat_ws函数比concat函数多了分隔符的功能，其实就是concat的升级版，假如分隔符为'',则取出来的结果和concat是一样的。concat_ws分隔符还支持多个字符作为分隔符的，日常用得更多的可能是||。
二、切割函数
1.split_part
a.语法介绍
1split_part(string text, delimiter text, field int) 2 3Split string on delimiter and return the given field (counting from one)
b.实际例子
1postgres=# select split_part('abc~@~def~@~ghi','~@~', 2); 2 split_part 3------------ 4 def 5(1 row) 6 7postgres=# select split_part('now|year|month','|',3); 8 split_part 9------------ 10 month 11(1 row)
c.说明
该函数对按分隔符去取某个特定位置上的值非常有效果
2.regexp_split_to_table
a.语法介绍
1regexp_split_to_table(string text, pattern text [, flags text]) 2 3Split string using a POSIX regular expression as the delimiter.
b.使用例子
1postgres=# SELECT regexp_split_to_table('kenyon,love,,china,!',','); 2 regexp_split_to_table 3----------------------- 4 kenyon 5 love 6 7 china 8 ! 9(5 rows) 10 11--按分割符切割 12postgres=# SELECT regexp_split_to_table('kenyon, china loves',E'\\s'); 13 regexp_split_to_table 14----------------------- 15 kenyon, 16 china 17 loves 18(3 rows) 19 20--按字母切割 21postgres=# SELECT regexp_split_to_table('kenyon,,china',E'\\s*'); 22 regexp_split_to_table 23----------------------- 24 k 25 e 26 n 27 y 28 o 29 n 30 , 31 , 32 c 33 h 34 i 35 n 36 a 37(13 rows)
3.regexp_split_to_array
a.语法介绍
1regexp_split_to_array(string text, pattern text [, flags text ]) 2 3Split string using a POSIX regular expression as the delimiter.
b.实际例子
1postgres=# SELECT regexp_split_to_array('kenyon,love,,china,!',','); 2 regexp_split_to_array 3-------------------------- 4 {kenyon,love,"",china,!} 5(1 row) 6 7postgres=# SELECT regexp_split_to_array('kenyon,love,,china!','s*'); 8 regexp_split_to_array 9----------------------------------------------- 10 {k,e,n,y,o,n,",",l,o,v,e,",",",",c,h,i,n,a,!} 11(1 row)
c.说明
上面用到的flag里的s*表示split all，E'\\s'表示转义空格