我有以下数据，想设置一个分隔符并将它们插入到单独的列中。
select * from name John | Kelly | Bick | HenrySteve | John | Max | Alis | Emily
select * from name
John | Kelly | Bick | Henry
Steve | John | Max | Alis | Emily
我希望我的输出是。。。
name1 name2 name3 name4 name5John Kelly Bick HenrySteve John Max Alis Emily
name1 name2 name3 name4 name5
John Kelly Bick Henry
Steve John Max Alis Emily
58wvjzkj1#
在postgres中，一个选择是 split_part() ，如果您事先知道每个字符串的最大值数。假设您的字符串列 col :
split_part()
col
select split_part(col, ' | ', 1) name1, split_part(col, ' | ', 2) name2, split_part(col, ' | ', 3) name3, split_part(col, ' | ', 4) name4, split_part(col, ' | ', 5) name5from name
select
split_part(col, ' | ', 1) name1,
split_part(col, ' | ', 2) name2,
split_part(col, ' | ', 3) name3,
split_part(col, ' | ', 4) name4,
split_part(col, ' | ', 5) name5
from name
a2mppw5e2#
您可以使用一次拆分 regexp_split_to_array() 然后从数组中提取元素：
regexp_split_to_array()
select ar[1] as name1, ar[2] as name2, ar[3] as name3, ar[4] as name4, ar[5] as name5from (values ('John | Kelly | Bick | Henry'), ('Steve | John | Max | Alis | Emily') ) v(names) cross join lateral regexp_split_to_array(v.names, ' \| ') ar;
select ar[1] as name1, ar[2] as name2, ar[3] as name3, ar[4] as name4, ar[5] as name5
from (values ('John | Kelly | Bick | Henry'),
('Steve | John | Max | Alis | Emily')
) v(names) cross join lateral
regexp_split_to_array(v.names, ' \| ') ar;
2条答案
按热度按时间58wvjzkj1#
在postgres中，一个选择是
split_part()，如果您事先知道每个字符串的最大值数。假设您的字符串列col:a2mppw5e2#
您可以使用一次拆分
regexp_split_to_array()然后从数组中提取元素：