postgresql 将逗号分隔的列数据拆分为其他列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8584967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split comma separated column data into additional columns
提问by Gallop
I have comma separated data in a column:
我在一列中有逗号分隔的数据：
Column
-------
a,b,c,d
I want to split the comma separated data into multiple columns to get this output:
我想将逗号分隔的数据拆分为多列以获取此输出：
Column1 Column2 Column3 Column4
------- ------- ------- -------
a b c d
How can this be achieved?
如何做到这一点？
回答by Erwin Brandstetter
split_part()does what you want in one step:
split_part()一步完成你想要的：
SELECT split_part(col, ',', 1) AS col1
, split_part(col, ',', 2) AS col2
, split_part(col, ',', 3) AS col3
, split_part(col, ',', 4) AS col4
FROM tbl;
Add as many lines as you have items in col(the possible maximum). Columns exceeding data items will be empty strings ('').
添加尽可能多的行col（可能的最大值）。超出数据项的列将是空字符串 ( '')。
回答by mu is too short
If the number of fields in the CSV is constant then you could do something like this:
如果 CSV 中的字段数不变，那么您可以执行以下操作：
select a[1], a[2], a[3], a[4]
from (
select regexp_split_to_array('a,b,c,d', ',')
) as dt(a)
For example:
例如：
=> select a[1], a[2], a[3], a[4] from (select regexp_split_to_array('a,b,c,d', ',')) as dt(a);
a | a | a | a
---+---+---+---
a | b | c | d
(1 row)
If the number of fields in the CSV is not constant then you could get the maximum number of fields with something like this:
如果 CSV 中的字段数不是恒定的，那么您可以获得最大的字段数，如下所示：
select max(array_length(regexp_split_to_array(csv, ','), 1))
from your_table
and then build the appropriate a[1], a[2], ..., a[M]column list for your query. So if the above gave you a max of 6, you'd use this:
然后a[1], a[2], ..., a[M]为您的查询构建适当的列列表。所以如果上面给你最多 6，你会使用这个：
select a[1], a[2], a[3], a[4], a[5], a[6]
from (
select regexp_split_to_array(csv, ',')
from your_table
) as dt(a)
You could combine those two queries into a function if you wanted.
如果需要，您可以将这两个查询组合成一个函数。
For example, give this data (that's a NULL in the last row):
例如，给出这个数据（最后一行是 NULL）：
=> select * from csvs;
csv
-------------
1,2,3
1,2,3,4
1,2,3,4,5,6
(4 rows)
=> select max(array_length(regexp_split_to_array(csv, ','), 1)) from csvs;
max
-----
6
(1 row)
=> select a[1], a[2], a[3], a[4], a[5], a[6] from (select regexp_split_to_array(csv, ',') from csvs) as dt(a);
a | a | a | a | a | a
---+---+---+---+---+---
1 | 2 | 3 | | |
1 | 2 | 3 | 4 | |
1 | 2 | 3 | 4 | 5 | 6
| | | | |
(4 rows)
Since your delimiter is a simple fixed string, you could also use string_to_arrayinstead of regexp_split_to_array:
由于您的分隔符是一个简单的固定字符串，您也可以使用string_to_array代替regexp_split_to_array：
select ...
from (
select string_to_array(csv, ',')
from csvs
) as dt(a);
Thanks to Michaelfor the reminder about this function.
感谢Michael提醒有关此功能的信息。
You really should redesign your database schema to avoid the CSV column if at all possible. You should be using an array column or a separate table instead.
如果可能的话，您确实应该重新设计数据库架构以避开 CSV 列。您应该改用数组列或单独的表。
回答by vicky
You can use split function.
您可以使用拆分功能。
SELECT
(select top 1 item from dbo.Split(FullName,',') where id=1 ) Column1,
(select top 1 item from dbo.Split(FullName,',') where id=2 ) Column2,
(select top 1 item from dbo.Split(FullName,',') where id=3 ) Column3,
(select top 1 item from dbo.Split(FullName,',') where id=4 ) Column4,
FROM MyTbl
