How to split a name into surname plus initials

Question

I have a Postgres table containing names like "Smith, John Albert", and I need to create a view which has names like "Smith, J A". Postgres has some regex implementations I haven't seen elsewhere.

So far I've got

SELECT regexp_replace('Smith, John Albert', '\Y\w', '', 'g');

which returns

S, J A

So I'm thinking I need to find out how to make the replace start part-way into the source string.

I could accept it if you did. It's the best solution to the problem. Could you include an explanation of the `\1`? — OutstandingBill, Jan 31 '17 at 19:18
@bub - of course! That's so much more obvious (to me) this morning than it was late last night :) — OutstandingBill, Jan 31 '17 at 19:22

Laurenz Albe · Answer 1 · 2017-01-31T16:15:33.467

2

The original idea is by Wiktor Stribiżew:

SELECT regexp_replace('Smith, John Albert', '^(\w+)|\Y\w', '\1', 'g');

 regexp_replace
----------------
 Smith, J A
(1 row)

edited Jan 31 '17 at 16:15

answered Jan 31 '17 at 14:20

Laurenz Albe

209,280
17
206
263

1

And it is my regex in fact. The second group makes no sense as it is not referred to from the replacement pattern. – Wiktor Stribiżew Jan 31 '17 at 16:03

score 2 · Accepted Answer · answered Jan 31 '17 at 19:27

The regex used in PostgreSQL is actually implemented using a software package written by Henry Spencer. It is not odd, it has its own advantages, peculiarities.

One of the differences from the usual NFA regex engines is the word boundary. Here, \Y matches a non-word boundary. The rest of the patterns you need are quite known ones.

So, you need to use '^(\w+)|\Y\w' pattern and a '\1' replacement.

Details:

^ - start of string anchor
(\w+) - Capturing group 1 matching 1+ word chars (this will be referred to with \1 from the replacement pattern)
| - or
\Y\w - a word char that is preceded with another word character.

The \1 is called a replacement numbered backreference, that just puts the value captured with Group 1 into the replacement result.

I meant "odd" as in different to the norm, but point taken, it also implies inferiority. Question edited. — OutstandingBill, Jan 31 '17 at 19:33

score 1 · Answer 3 · answered Jan 31 '17 at 13:19

1

As @bub suggested:

t=# SELECT concat(split_part('Smith, John Albert',',',1),',',regexp_replace(split_part('Smith, John Albert',',',2), '\Y\w', '', 'g'));
   concat
------------
 Smith, J A
(1 row)

answered Jan 31 '17 at 13:19

Vao Tsun

47,234
13
100
132

Works fine and was first in, but is less elegant than @Wiktor Stribiżew's solution, so I'll accept Laurenz Albe's one. Thank you – OutstandingBill Jan 31 '17 at 19:12
@OutstandingBill sure thing – Vao Tsun Jan 31 '17 at 21:00

How to split a name into surname plus initials

3 Answers3

Linked