PostgreSQL database cutting and combining field functions

Postgresql has many built-in practical functions. The following describes the grouping and cutting functions.
Environment: PostgreSQL 9.1.2
CentOS 5.7 final

I. Composite Functions
1. concat
A. Introduction

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. Actual Example:

postgres=# create table t_kenyon(id int,name varchar(10),remark text);CREATE TABLEpostgres=# insert into t_kenyon values(1,'test','kenyon'),(2,'just','china'),(3,'iam','lovingU');INSERT 0 3postgres=# insert into t_kenyon values(4,'test',null);INSERT 0 1postgres=# insert into t_kenyon values(5,null,'adele');INSERT 0 1postgres=# 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 is a concatenation function. null values can be ignored. The concatenation value has no separator. If a Delimiter is required, use the following concat_ws function.

2. concat_ws
A. Introduction

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. Practical Application

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. The concat_ws function has more separator functions than the concat function. In fact, it is an upgraded version of concat. If the separator is '', the result obtained is the same as that of concat. Its functions are similar to those of the group_concat function in mysql, but they are also different. In pg, The concat_ws separator also supports multiple characters as the separator, which is more likely to be used |.

Ii. Cut Functions
1. split_part
A. Introduction

split_part(string text, delimiter text, field int)Split string on delimiter and return the given field (counting from one)

B. Actual Example

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. It indicates that this function is very effective in obtaining values at a specific position by separators.

2. regexp_split_to_table
A. Introduction

regexp_split_to_table(string text, pattern text [, flags text])Split string using a POSIX regular expression as the delimiter.

B. Example

S = # SELECT regexp_split_to_table ('kenyon, love, china ,! ',', '); Regexp_split_to_table ----------------------- kenyon love china! (5 rows) -- cut postgres = # SELECT regexp_split_to_table ('kenyon, china, loves ',', '); regexp_split_to_table limit kenyon china loves (3 rows) -- cut by letter 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. Introduction

regexp_split_to_array(string text, pattern text [, flags text ])Split string using a POSIX regular expression as the delimiter.

B. Actual Example

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. Description
S * in the flag used above indicates split all