CODEWITHSUNDEEP
regexpostgresql
OutstandingBill
OutstandingBill

Reputation: 2844

How to split a name into surname plus initials

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.

Upvotes: 1

Views: 392

Answers (3)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626893

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.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246588

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)

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51509

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)

Upvotes: 1

Related Questions