regexp_split_to_table 与 regexp_split_to_array

regexp_split_to_table实例

• 实例

select regexp_split_to_table(coalesce('/home/postgres/test.sh',''),E'\/'); 
select n from generate_series(1,5,2) n;
select regexp_split_to_table(coalesce('/home/postgres/test.sh',''),E'\/'), 
  n from generate_series(1,5,2) n;

• 结果



• 
  



• 
  

regexp_split_to_array实例

• 实例

select  regexp_split_to_array('how,are,you,doing',',');
select n from generate_series(1,5,2) n;
select  regexp_split_to_array('how,are,you,doing',','),
        n from generate_series(1,5,2) n;

• 结果



• 
  



• 
  

实例3

CREATE TABLE tbl_user(
    f_user_name character varying(20) NOT NULL,
    f_interest character varying(100),
    CONSTRAINT t_ttt_pkey PRIMARY KEY (f_user_name)
);
-- 数据
INSERT INTO tbl_user(f_user_name, f_interest) VALUES ('张三', '足球,篮球,羽毛球');
INSERT INTO tbl_user(f_user_name, f_interest) VALUES ('李四', '篮球,排球');
select * from tbl_user; 

• 结果

select f_user_name, regexp_split_to_table(f_interest, ',') as tab_interest from tbl_user;

• 结果

select
    t.f_user_name,
    t.tab_interest
from (
    select f_user_name, regexp_split_to_table(f_interest, ',') as tab_interest from tbl_user
) t
where t.tab_interest = '篮球';

• 结果