Postgresql has many built-in practical functions. The following describes the next combination and cutting function environment: PostgreSQL9.1.2CENTOS5.7final 1. composite functions 1. concata. introduction to concat (strany [, strany [,...]) concatenateallbutfirstargumentswithseparators. thefirstparameteris
Postgresql has many built-in practical functions. The following describes the next combination and cutting function environment: PostgreSQL 9.1.2 CENTOS 5.7 final 1. composite functions 1. concat. introduction to concat (str any [, str any [,...]) concatenate all but first arguments with separators. the first parameter is
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 TABLE
S = # insert into t_kenyon values (1, 'test', 'kenyon'), (2, 'just ', 'China'), (3, 'iam ', 'lovingu ');
INSERT 0 3
S = # insert into t_kenyon values (4, 'test', null );
INSERT 0 1
S = # insert into t_kenyon values (5, null, 'adre ');
INSERT 0 1
S = # 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
-------------
1 testkenyon
2 justchina
3 iamlovingU
4 test
5 adele
(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
S = # 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)
S = # 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)
S = # select concat_ws ('', id, name, remark) from t_kenyon;
Concat_ws
-------------
1 testkenyon
2 justchina
3 iamlovingU
4 test
5 adele
(5 rows)
S = # 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 by delimiter
S = # SELECT regexp_split_to_table ('kenyon, china, loves ',',');
Regexp_split_to_table
-----------------------
Kenyon
China
Loves
(3 rows)
-- Cut by letter
S = # 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
S = # 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, ,!}
(1 row)
C. Description
S * in the flag used above indicates split all
