修复Amazon Redshift中拆分全名列为名、中间名、姓的SQL问题

原SQL的核心问题是通过全局字符串替换提取中间名，当中间名包含与名/姓重复的子串时，会被错误移除。比如'john john doe'这类场景，原逻辑会把中间的john也删掉，导致中间名为空。

修复后的SQL方案（正则表达式实现） #

SELECT 
    fullname,
    -- 提取FirstName：字符串开头到第一个空格前的内容
    REGEXP_SUBSTR(fullname, '^[^\\s]+') AS FirstName,
    -- 提取MiddleName：移除开头的FirstName和结尾的LastName后，清理首尾空格
    RTRIM(LTRIM(REGEXP_REPLACE(fullname, '^[^\\s]+\\s+|\\s+[^\\s]+$', ''))) AS MiddleName,
    -- 提取LastName：字符串末尾最后一个空格后的内容
    REGEXP_SUBSTR(fullname, '[^\\s]+$') AS LastName
FROM (
    -- 测试示例数据
    SELECT 'john john doe' AS fullname UNION ALL
    SELECT 'alice marie smith' AS fullname UNION ALL
    SELECT 'bob bobson' AS fullname UNION ALL
    SELECT 'charlie charlie charlie' AS fullname
) AS name_samples;

方案说明 #

1. FirstName提取：用REGEXP_SUBSTR匹配字符串开头的所有非空格字符，精准获取第一个词，不会受后续重复内容影响。
2. LastName提取：用REGEXP_SUBSTR匹配字符串末尾的最后一个非空格词，同样不受前面重复内容干扰。
3. MiddleName提取：通过正则替换直接移除开头的FirstName+空格和结尾的空格+LastName，保留中间的所有内容，再清理首尾冗余空格。这种方式不会误删中间名里与名/姓重复的子串。

测试结果 #

针对示例数据，输出如下：

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