如何用单个CTE在SQL中拆分姓名列？求更优实现方案

需求是将格式为LastName, FirstName (EmpId)的REPORT_TO列，拆分为单独的姓（LNAME）和名（FNAME）列。以下提供单个CTE的实现方式，以及更高效的无CTE写法：

单个CTE实现 #

无需嵌套两个CTE，在一个CTE内完成两次字符串截取即可：

IF OBJECT_ID('tempdb..#tempMyTable') IS NOT NULL
    DROP TABLE #tempMyTable

CREATE TABLE #tempMyTable(
    REPORT_TO varchar(25) not null
)
INSERT INTO #tempMyTable
VALUES
('MILLER, BEN (BMR09876)'),
('LON, JOHN (JLON45098)'),
('WILSON, MARY (MWN2345)');
    
WITH CTE_SplitNames AS (
    SELECT 
        REPORT_TO,
        -- 提取姓氏：从字符串开头截取到逗号前的部分
        LEFT(REPORT_TO, CHARINDEX(',', REPORT_TO) - 1) AS LNAME,
        -- 提取名字：先截取逗号后的所有内容，再从该内容中截取到左括号前，最后去除前置空格
        LTRIM(LEFT(RIGHT(REPORT_TO, LEN(REPORT_TO) - CHARINDEX(',', REPORT_TO)), CHARINDEX('(', RIGHT(REPORT_TO, LEN(REPORT_TO) - CHARINDEX(',', REPORT_TO))) - 1)) AS FNAME
    FROM #tempMyTable
)
SELECT * FROM CTE_SplitNames

更优无CTE实现（SQL Server 2008+兼容） #

直接在SELECT语句中完成拆分，逻辑更简洁，执行效率也更高：

IF OBJECT_ID('tempdb..#tempMyTable') IS NOT NULL
    DROP TABLE #tempMyTable

CREATE TABLE #tempMyTable(
    REPORT_TO varchar(25) not null
)
INSERT INTO #tempMyTable
VALUES
('MILLER, BEN (BMR09876)'),
('LON, JOHN (JLON45098)'),
('WILSON, MARY (MWN2345)');

SELECT 
    REPORT_TO,
    LEFT(REPORT_TO, CHARINDEX(',', REPORT_TO) - 1) AS LNAME,
    -- 计算名字的起始位置（逗号后一位）和长度（左括号位置减去逗号位置再减1），截取后去除前置空格
    LTRIM(SUBSTRING(REPORT_TO, CHARINDEX(',', REPORT_TO) + 1, CHARINDEX('(', REPORT_TO) - CHARINDEX(',', REPORT_TO) - 1)) AS FNAME
FROM #tempMyTable

关键说明 #

• LTRIM用于清除名字开头的空格（原格式中逗号与名字之间有空格）
• 通过CHARINDEX分别定位逗号和左括号的位置，精准计算需要截取的字符串范围，避免多层嵌套的RIGHT/LEFT调用

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