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 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
