CODEWITHSUNDEEP
postgresql
fLen
fLen

Reputation: 598

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

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?

Upvotes: 1

Views: 6387

Answers (2)

Patrick
Patrick

Reputation: 32244

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.

Upvotes: 3

peterm
peterm

Reputation: 92795

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

Upvotes: 1

Related Questions