postgresql 将逗号分隔的字符串转为多行的实例

SELECT unnest(string_to_array('4513,4564,555',',')) as a1;

SELECT unnest(ARRAY[1,2]);
SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);

 SELECT string_agg(att.attname,',' order by attrnums) as distribution
  FROM gp_distribution_policy a,pg_attribute att
  WHERE a.localoid ='sor.wpp_adefect_f_n'::regclass
  and a.localoid = att.attrelid
  and att.attnum = any(a.attrnums);

select attname,attnum from pg_attribute where attrelid='26625' order by attnum asc;

select string_agg(attname,',' order by attnum) from pg_attribute where attrelid='26625' and attnum >0;

regexp_split_to_table(string, pattern [, flags ])

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s') AS foo;
SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*"" src="/UploadFiles/2021-04-09/20210201094415.jpg">

-----------------------update 2019年10月23日11:37:32-----------------------
imagePath字符串切割处理

如果想将如上的长列转换为多行，可通过regexp_split_to_table()来处理。处理的结果如下。

sql形如:


select regexp_split_to_table(image_path,E'\\ ') pattern from (select distinct panel_id, 'Y:\\' || prod_id || '\\' || substring( glass_id, 0, 6 )|| '\\' || substring( glass_id, 0, 9 )|| '\\' || panel_id || '\\' || 'big' || ' '||
 'Y:\\' || prod_id || '\\' || substring( glass_id, 0, 6 )|| '\\' || substring( glass_id, 0, 9 )|| '\\' || panel_id || '\\' || 'small'|| ' '||
 'Y:\\' || prod_id || '\\' || substring( glass_id, 0, 6 )|| '\\' || substring( glass_id, 0, 9 )|| '\\' || panel_id || '\\' || 'IMAGE' as image_path 
from tabelName
where 1 = 1
and time>= '2019-09-09 00:00:00'
and time<= '2019-09-10 08:00:00'
)A


3、字符串分割符


split_part(str,',',1)
select split_part('A3332-22222222','-',1);  - > A3332 
select split_part('A3332-22222222','-',2);  - > 22222222



这个比较简单一看便知道了。