How to get initials easily out of text field using Postgres

Question

I am using Postgres version 9.4 and I have a full_name field in a table.

In some cases, I want to put initials instead of the full_name of the person in my table.

Something like:

Name        | Initials
------------------------
Joe Blow    | J. B.  
Phil Smith  | P. S.  

The full_name field is a string value (obviously) and I think the best way to go about this is to split the string into an array foreach space i.e.:

select full_name, string_to_array(full_name,' ') initials
from my_table

This produces the following result-set:

Eric A. Korver;{Eric,A.,Korver}
Ignacio Bueno;{Ignacio,Bueno}
Igmar Mendoza;{Igmar,Mendoza}

Now, the only thing I am missing is how to loop through each array element and pull the 1st character out of it. I will end up using substring() to get the initial character of each element - however I am just stuck on how to loop through them on-the-fly..

Anybody have a simple way to go about this?

Answer 1

Use unnest with 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)

In Postgres 14+ you can replace unnest(string_to_array(...)) with string_to_table(...).

Test it in db<>fiddle.