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.

Answer 2

You can also create a helper function for this, in case you want to use similar logic in multiple queries. Check this out

--
-- Function to extract a person's initials from the full name.
--

DROP FUNCTION IF EXISTS get_name_initials(TEXT);

CREATE OR REPLACE FUNCTION get_name_initials(full_name TEXT)
RETURNS TEXT AS $$
DECLARE 
    result TEXT :='';
    part VARCHAR :='';
BEGIN
    FOREACH part IN ARRAY string_to_array($1, ' ') LOOP
        result :=  result || substr(part, 1, 1) || '.';
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Now you can simply use this function to get the initials like this.

SELECT full_name, get_name_initials(full_name) as initials
FROM my_table;

SELECT get_name_initials('Phil Smith'); -- Returns P. H. 
SELECT get_name_initials('Joe Blow'); -- Returns J. B.