将逗号分隔的列数据拆分为其他列

小开

最佳答案

If the number of fields in the CSV is constant then you could do something like this:

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:

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:

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):

=> 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_array instead of regexp_split_to_array:

select ...
from (
select string_to_array(csv, ',')
from csvs
) as dt(a);

Thanks to Michael for the reminder about this function.

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.

小开

split_part() does what you want in one step:

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 ('').

小开

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