SQL Server 如何从员工姓名字段提取首字母拼接姓氏，适配多姓名场景

核心逻辑都是先拆分多姓名、逐个处理单姓名、再合并回原格式，不同工具的实现方式如下：

SQL实现（以MySQL 8.0+为例） #

先通过递归CTE拆分逗号分隔的多个姓名，处理后再按ID分组合并：

WITH RECURSIVE split_names AS (
    SELECT 
        ID,
        Employees AS full_names,
        1 AS pos
    FROM your_table
    UNION ALL
    SELECT 
        ID,
        full_names,
        pos + 1
    FROM split_names
    WHERE pos <= LENGTH(full_names) - LENGTH(REPLACE(full_names, ', ', '')) + 1
)
SELECT 
    ID,
    GROUP_CONCAT(
        CONCAT(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(full_names, ', ', pos), ', ', -1), 1), '. ', SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(full_names, ', ', pos), ', ', -1), ' ', -1))
        SEPARATOR ', '
    ) AS Employees
FROM split_names
GROUP BY ID;

如果是PostgreSQL等支持字符串拆分函数的数据库，可直接用string_to_table拆分行，逻辑一致更简洁。

Python Pandas实现 #

自定义处理函数对每行Employees字段做转换：

import pandas as pd

def convert_employees(name_str):
    names = name_str.split(', ')
    res = []
    for full_name in names:
        first_name, last_name = full_name.split(' ', 1)
        res.append(f"{first_name[0]}. {last_name}")
    return ', '.join(res)

# 读取表格后调用转换函数
df = pd.read_excel('your_file.xlsx') # 或pd.read_csv等其他读取方式
df['Employees'] = df['Employees'].apply(convert_employees)

Excel实现（365/2021及以上版本） #

使用TEXTJOIN数组公式直接处理单元格内容，假设Employees字段在A列，公式写在B1：

=TEXTJOIN(", ",TRUE,LEFT(TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)),SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,", ",""))+1,,1,99)),1)&". "&TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)),SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,", ",""))+1,,1,99))," ",REPT(" ",99)),99)))

输入完成直接回车即可自动适配单个/多个姓名的转换。

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