PostgreSQL 9.6如何将单字段全名拆分为名、中间名、姓三个字段

核心逻辑 #

基于空格拆分trim后的用户名字段，按以下规则分配：

• 名：固定取拆分后的第一个片段
• 姓：固定取拆分后的最后一个片段
• 中间名：拆分后总片段数≥3时，取中间部分（支持单个/多个中间名场景），否则留空

单中间名适配方案（匹配你提供的样例场景） #

如果你确认所有全名最多只有1个中间名，可以用以下实现，执行前建议先运行SELECT语句验证结果，确认符合预期再执行更新：

-- 结果验证查询
SELECT 
    usr_name,
    SPLIT_PART(TRIM(usr_name), ' ', 1) AS calculated_firstname,
    CASE WHEN ARRAY_LENGTH(STRING_TO_ARRAY(TRIM(usr_name), ' '), 1) >=3 
         THEN SPLIT_PART(TRIM(usr_name), ' ', 2) 
         ELSE '' END AS calculated_middlename,
    SPLIT_PART(TRIM(usr_name), ' ', -1) AS calculated_lastname
FROM app_user;

验证无误后执行更新：

UPDATE app_user
SET 
    usr_firstname = SPLIT_PART(TRIM(usr_name), ' ', 1),
    usr_middlename = CASE WHEN ARRAY_LENGTH(STRING_TO_ARRAY(TRIM(usr_name), ' '), 1) >=3 
                          THEN SPLIT_PART(TRIM(usr_name), ' ', 2) 
                          ELSE '' END,
    usr_lastname = SPLIT_PART(TRIM(usr_name), ' ', -1);

样例运行结果 #

对你提供的测试数据执行后结果如下：

多中间名适配方案 #

如果存在包含多个中间名的场景（例如Anna Maria Louise Smith），需要将所有中间部分合并存入中间名字段，可以用以下实现：

-- 结果验证查询
SELECT 
    usr_name,
    SPLIT_PART(TRIM(usr_name), ' ', 1) AS calculated_firstname,
    CASE WHEN ARRAY_LENGTH(STRING_TO_ARRAY(TRIM(usr_name), ' '), 1) >=3 
         THEN REGEXP_REPLACE(TRIM(usr_name), '^[^ ]+ (.*) [^ ]+$', '\1')
         ELSE '' END AS calculated_middlename,
    SPLIT_PART(TRIM(usr_name), ' ', -1) AS calculated_lastname
FROM app_user;

对应的更新语句只需要把中间名的赋值逻辑替换为上述正则写法即可。

内容的提问来源于stack exchange，提问作者Andrey