postgresql 如何拆分由分隔的两个字符串;在两个新栏中

8ftvxx2r 于 2022-12-03 发布在 PostgreSQL

关注(0)|答案(3)|浏览(178)

我有一个命令

SELECT something, string_agg(other, ';') FROM table
GROUP BY something HAVING COUNT(*)>1;

但我不知道如何将其分为两列，因为它不将string_agg视为一列。
这是我的原件

something | other |         
--------+--------+
 example  | yes, no   |  
 using  | why, what  |

我想要这个

something | other | new        
--------+--------+------
 example  | yes   | no     
 using  | why     | what

postgresql

来源：https://stackoverflow.com/questions/74628306/how-to-split-two-strings-separated-by-a-in-two-new-columns

3条答案

按热度按时间

h79rfbju1#

我们可以在这里使用正则表达式：

SELECT
    something,
    SUBSTRING(other FROM '[^,]+') AS other,
    REGEXP_REPLACE(other, '.*,[ ]*', '') AS new
FROM yourTable;

赞(0）回复(0）举报 2022-12-03

nbysray52#

我将把它聚合到一个数组中：

select something, 
       others[1] as other, 
       others[2] as "new"
from (
  SELECT something, array_agg(other) as others
  FROM table
  GROUP BY something 
  HAVING COUNT(*)>1
) x

赞(0）回复(0）举报 2022-12-03

vnjpjtjt3#

备选方案：只需要使用split_part()函数。我结合trim（）函数来删除前导/尾随空格。（参见demo）

select something
     , trim(split_part(other, ',', 1)) other
     , trim(split_part(other, ',', 2)) new 
  from table;

赞(0）回复(0）举报 2022-12-03

我来回答

postgresql 如何拆分由分隔的两个字符串;在两个新栏中

3条答案

相关问题

热门标签

最新问答