提取所有以相同字符为界的子字符串。

Question

给定一个文本的name_loc列，如下所示：

{"Charlie – White Plains, NY","Wrigley – Minneapolis, MN","Ana – Decatur, GA"}

我试图提取名字，最好用逗号隔开：

Charlie, Wrigley, Ana

我已经走了这么远：

SELECT SUBSTRING(CAST(name_loc AS VARCHAR) from '"([^ –]+)')
FROM table;

回传

Charlie

如何扩展此查询以提取所有名称？

Answer 1

您的字符串文本恰好是一个有效的数组文字。

(也许不是巧合？列应该是text[]类型(首先？)

如果这是可靠的格式，那么有一个安全而简单的解决方案：

SELECT t.id, x.names
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(split_part(elem, ' – ', 1), ', ') AS names
   FROM   unnest(t.name_loc::text[]) elem
   ) x;

或者：

SELECT id, string_agg(split_part(elem, ' – ', 1), ', ') AS names
FROM   (SELECT id, unnest(name_loc::text[]) AS elem FROM tbl) t 
GROUP  BY id;

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_15&fiddle=9b019061fd8c7a0fc17c60d82346e446

步骤

1. 使用unnest()在LATERAL CROSS JOIN中或直接在SELECT列表中卸载数组。

- [What is the difference between LATERAL JOIN and a subquery in PostgreSQL?](https://stackoverflow.com/questions/28550679/what-is-the-difference-between-lateral-join-and-a-subquery-in-postgresql/28557803#28557803)

1. 以split_part()作为第一部分。我选择' – '作为分隔符，而不仅仅是' '，以便允许具有嵌套空间的名称，如"Anne“。请参见：

- [Split comma separated column data into additional columns](https://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns/8612456#8612456)

1. 用string_agg()聚合结果。我没有添加特别的订单，因为您没有指定一个。

- [Concatenate multiple result rows of one column into one, group by another column](https://stackoverflow.com/questions/15847173/concatenate-multiple-result-rows-of-one-column-into-one-group-by-another-column/15850510#15850510)