這篇文章主要介紹了postgresql 將逗號(hào)分隔的字符串轉(zhuǎn)為多行的實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
-- 將逗號(hào)分隔的字符串轉(zhuǎn)為多行
1SELECT unnest(string_to_array('4513,4564,555',',')) as a1;
-- array轉(zhuǎn)為行
1
2SELECT unnest(ARRAY[1,2]);SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);
補(bǔ)充：PostgreSQL 行轉(zhuǎn)列、列轉(zhuǎn)行字符串函數(shù)、字符串分割函數(shù)
本文主要介紹PostgreSQL 的行轉(zhuǎn)列、列轉(zhuǎn)行以及字符串切割函數(shù)，實(shí)際業(yè)務(wù)中對(duì)前兩個(gè)均有使用，并配有實(shí)際例子參考。
1、字符串列轉(zhuǎn)行
string_agg，某些地方也稱為字符串聚合操作。
如果需要按照一個(gè)字符串按照某個(gè)分割符拼接起來(lái)。
例如：
按照id把字符串按照指定分隔符拼接起來(lái)。實(shí)際業(yè)務(wù)中有什么需要這種字符串函數(shù)的呢？
-----------------------update 2020年1月16日17:05:59-----------------------
這里學(xué)習(xí)一個(gè)和系統(tǒng)表有關(guān)的查詢，用到了stragg ，用法之妙，自己體會(huì)。
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);
1select attname,attnum from pg_attribute where attrelid='26625' order by attnum asc;
可以看到每個(gè)表上除了可見(jiàn)的列之外還有attrnums < 1的列。這幾個(gè)列用來(lái)實(shí)現(xiàn)mvcc、表明數(shù)據(jù)的物理位置、數(shù)據(jù)所處segID...
1select string_agg(attname,',' order by attnum) from pg_attribute where attrelid='26625' and attnum >0;
將所有可見(jiàn)列查詢出來(lái)拼接sql，屢試不爽。
2、字符串行轉(zhuǎn)列
1regexp_split_to_table(string, pattern [, flags ])
regexp_split_to_table(string, pattern [, flags ])。如果沒(méi)有與pattern的匹配，該函數(shù)返回string。
如果有至少有一個(gè)匹配，對(duì)每一個(gè)匹配它都返回從上一個(gè)匹配的末尾（或者串的開(kāi)頭）到這次匹配開(kāi)頭之間的文本。當(dāng)沒(méi)有更多匹配時(shí)，它返回從上一次匹配的末尾到串末尾之間的文本。
flags參數(shù)是一個(gè)可選的文本串，它包含零個(gè)或更多單字母標(biāo)志，這些標(biāo)識(shí)可以改變?cè)摵瘮?shù)的行為。
這個(gè)標(biāo)識(shí)有很多，具體可查看http://postgres.cn/docs/9.6/functions-matching.html。不過(guò)該參數(shù)是可以省略的，我看到很少用這個(gè)flg的。
E是Posix樣式轉(zhuǎn)義字符串的前綴。現(xiàn)代Postgres通常不需要這個(gè),此處的E可有可無(wú)。
--\\s+ 可匹配至少一個(gè)空白字符。
--\\s 表示空白字符。包括，空格，制表符等
--\s*
--*是貪婪模式,會(huì)盡可能匹配更多的字符
--而*?是非貪婪模式 會(huì)盡量匹配少的字符
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*?') AS foo;
關(guān)于正則表達(dá)式計(jì)劃以專題來(lái)學(xué)習(xí)
幾個(gè)元字符是需要記住的：
-----------------------update 2019年10月23日11:37:32-----------------------
imagePath字符串切割處理
如果想將如上的長(zhǎng)列轉(zhuǎn)換為多行，可通過(guò)regexp_split_to_table()來(lái)處理。處理的結(jié)果如下。
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
文章來(lái)源：腳本之家
來(lái)源地址：https://www.jb51.net/article/205150.htm
申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！
