如何将名字分成姓氏和首字母 [英] How to split a name into surname plus initials

问题描述

我有一个Postgres表，其中包含诸如 Smith，John Albert的名称，并且我需要创建一个具有诸如 Smith，J A的名称的视图。 Postgres有一些 regex实现，我在其他地方都没见过。

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.

到目前为止，我已经

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

返回

S, J A

所以我在想我需要找出制作方法

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

推荐答案

regex 实际上是使用Henry Spencer编写的软件包来实现的。这并不奇怪，它具有自己的优点和独特性。

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.

与通常的NFA regex引擎的区别之一是单词边界。在这里， \Y 匹配非单词边界。您需要的其余模式都是众所周知的。

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.

因此，您需要使用'^（\w +）| \Y \w'模式和'\1'替换。

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

详细信息：

• ^ -字符串锚点开始


• （\w +）- 捕获组 1个匹配的1个以上字符的字符（将从 \1 引用


• | -或


• \ Y\w -一个以另一个单词字符开头的单词char。

• ^ - 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.

\1 被称为替换编号的反向引用 ，它只是将第1组捕获的值放入替换结果中。

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

这篇关于如何将名字分成姓氏和首字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！