postgresSQL多种字符串分割及查询汇总

 

目录

表及数据准备

SPLIT_PART函数应用及说明

string_to_array函数应用及说明

基于指定规则查询该列所有

按需分割后产生的字符串单独判断

按需分割后产生的字符串及其它列混合查询

以按需分割查询为条件查询其它列

array_length函数应用及说明

---

 

表及数据准备

测试表

create table flag_info(
id     int
,name varchar(20)    default ''    -- xx标志
,value             int    default 0            
,PRIMARY KEY(id)
);

插入部分数据

insert into flag_info values(1,'a.b.c', 127);
insert into flag_info values(2,'a.b.d', 6);
insert into flag_info values(3,'a.c.d', 91);
insert into flag_info values(4,'b.c.d', 97);
insert into flag_info values(5,'b.c.e', 5);

看看所有数据：

 select * from flag_info;
 id | name  | value 
----+-------+-------
  1 | a.b.c |   127
  2 | a.b.d |     6
  3 | a.c.d |    91
  4 | b.c.d |    97
  5 | b.c.e |     5
(5 rows)

 

SPLIT_PART函数应用及说明

 

1，查询name分割后第二位值=c的所有记录：

# select * from flag_info where SPLIT_PART(name,'.',2) = 'c';
 id | name  | value 
----+-------+-------
  3 | a.c.d |    91
  4 | b.c.d |    97
  5 | b.c.e |     5
(3 rows)

2，查询name分割后第1位值=a的所有记录：

#select * from flag_info where SPLIT_PART(name,'.',1) = 'a';
 id | name  | value 
----+-------+-------
  1 | a.b.c |   127
  2 | a.b.d |     6
  3 | a.c.d |    91
(3 rows)

注意：第几位不从0开始，是从1开始的。

再来，插入一些不包含.的值：

insert into flag_info values(6,'a', 1);
insert into flag_info values(7,'a.b', 1);

现在共7条数据：

#select * from flag_info;
 id | name  | value 
----+-------+-------
  1 | a.b.c |   127
  2 | a.b.d |     6
  3 | a.c.d |    91
  4 | b.c.d |    97
  5 | b.c.e |     5
  6 | a     |     1
  7 | a.b   |     1
(7 rows)

继续：

#select * from flag_info where SPLIT_PART(name,'.',1) = 'a';
 id | name  | value 
----+-------+-------
  1 | a.b.c |   127
  2 | a.b.d |     6
  3 | a.c.d |    91
  6 | a     |     1
  7 | a.b   |     1
(5 rows)

可以看到id=6的也查出来了，id=6的值不包含.   说明分割后默认a就是第1位，同样会被查询出来。

 

string_to_array函数应用及说明

 

基于指定规则查询该列所有

select  string_to_array(name,'.') from flag_info;
 {a,b,c}
 {a,b,d}
 {a,c,d}
 {b,c,d}
 {b,c,e}
 {a}
 {a,b}
(7 rows)

查询后返回的是一个array.

 

按需分割后产生的字符串单独判断

比如获取分割后每个数组的第一个元素：

select ss[1] from (select string_to_array(name,'.')  ss from flag_info ) f;
 ss 
----
 a
 a
 a
 b
 b
 a
 a
(7 rows)

 

按需分割后产生的字符串及其它列混合查询

除了name分割后的第一个元素，id也想获取怎么办？

select id,ss[1] from (select id,string_to_array(name,'.')  ss from flag_info ) f;
 id | ss 
----+----
  1 | a
  2 | a
  3 | a
  4 | b
  5 | b
  6 | a
  7 | a

以按需分割查询为条件查询其它列

获取第一个元素为a的所有记录的id及name原值：

select id,name from (select id,name,string_to_array(name,'.')  ss from flag_info) f where ss[1]='a';
 id | name  
----+-------
  1 | a.b.c
  2 | a.b.d
  3 | a.c.d
  6 | a
  7 | a.b
(5 rows

再比如只获取分割后第一个元素为a的id：

select id from (select id,string_to_array(name,'.')  ss from flag_info) f where ss[1]='a';
 id 
----
  1
  2
  3
  6
  7
(5 rows)

array_length函数应用及说明

 

计算所有记录name列分割后的数组长度，并输出id及原name：

select id,name,array_length(string_to_array(name,'.'),1) len from flag_info;
 id | name  | len 
----+-------+-----
  1 | a.b.c |   3
  2 | a.b.d |   3
  3 | a.c.d |   3
  4 | b.c.d |   3
  5 | b.c.e |   3
  6 | a     |   1
  7 | a.b   |   2
(7 rows)

 

分享无涯，祝君快乐！