PostgreSQL: How to get the first letter of each words and add period [.] after?

Question

I'm trying to get the first letter word and put add period after it.

Here is my sample data of a middlename:

Dela Torre
Delacruz
De Los Santos

I need to have result of:

D.T.
D.
D.L.S

I do try a sample query like this but only the first letter I could get.

cast(a.first_name  ||' ' ||  
SUBSTRING (a.middle_name, 1,1) ||'. '|| 
a.last_name   as text) as name

RESULT: first_name D. last_name

How can I able to do that?

You need to split middle name into array (like here: http://stackoverflow.com/a/8585472/1300049) and then get the first letter of each element — JleruOHeP, Sep 06 '16 at 03:04

Patrick · Answer 1 · 2016-09-06T05:32:11.773

If your strings are nicely formatted starting with a capital letter and lower-case letters otherwise, you can use a regular expression to do this:

SELECT regexp_replace(middle_name, '([a-z ])+', '.', 'g');

The regular expression "says": take one or more subsequent lowercase letters or spaces and replace it with a single dot .. The 'g' (global) modifier repeats this for all occurrences.

In combination with first_name and last_name:

SELECT concat_ws(' ', first_name, 
                 regexp_replace(middle_name, '([a-z ])+', '.', 'g'),
                 last_name
                ) AS name
FROM my_table;

If - as seems to be the case - the name can be any combination of upper-case and lower-case letters then you must split the name into an array, take the first letter and re-assemble it:

SELECT concat_ws(' ', first_name, middle, last_name) AS name
FROM (
    SELECT first_name, string_agg(left(middle, 1), '.') AS middle, last_name
    FROM my_table, unnest(string_to_array(middle_name, ' ')) m(middle)
    GROUP BY first_name, last_name) sub;

This assumes that there are no duplicate (first_name, last_name) pairs. If so, you should include a primary key or some other unique row identifier.

I try it but the result became: first_name DELA.CRUZ last_name, it shoud be first_name D.L. last_name — fLen, Sep 06 '16 at 05:09
Apparently your middle names are capitalized, unlike what you posted in your question. See updated answer. — Patrick, Sep 06 '16 at 05:32

score 1 · Accepted Answer · answered Sep 06 '16 at 03:28

One way to do it

SELECT id, first_name || ' ' || string_agg(left(m, 1) || '.', '') || ' ' || last_name AS name
  FROM (
    SELECT id, first_name, unnest(string_to_array(middle_name, ' ')) m, last_name
      FROM table1
  ) q
 GROUP BY id, first_name, last_name

Here is a SQLFiddle

PostgreSQL: How to get the first letter of each words and add period [.] after?

2 Answers2