PostgreSQL 切割、組合字段函數

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是一樣的。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',E'\\s');

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.實際例子