在postgres中，一个选择是 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) name5
from name

您可以使用一次拆分 regexp_split_to_array() 然后从数组中提取元素：

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;