unnest
搭配使用string_agg
:
select full_name, string_agg(substr(initials, 1,1)||'.', ' ') initials
from (
select full_name, unnest(string_to_array(full_name,' ')) initials
from my_table
) sub
group by 1;
full_name | initials
------------------------+-------------
Phil Smith | P. S.
Joe Blow | J. B.
Jose Maria Allan Pride | J. M. A. P.
Eric A. Korver | E. A. K.
(4 rows)