数据库sql组合和切割函数

冷不防 2023-10-18 19:19 123阅读 0赞

**Table of Contents**

一.组合函数

1.concat

2.concat\_ws

二、切割函数

1.split\_part

2.regexp\_split\_to\_table

3.regexp\_split\_to\_array

三：多行结果相加

--------------------

# **一.组合函数** #

#    **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.实际例子

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，E'\\\\s'表示转义空格

# 三：多行结果相加 #

group\_concat（字段）

select group\_concat(id) from xxxxxtable

注：该函数结果默认限制为1024

可以通过sql查看：show variables like 'group\_concat\_max\_len';

可以暂时通过语句更改，但重启后恢复了就

SET GLOBAL group\_concat\_max\_len = 4294967295;

SET SESSION group\_concat\_max\_len = 4294967295;