如何使用T-SQL拆分ClientID字符串提取姓名与档案编号？

T-SQL拆分ClientID字段实现方案 #

需求说明 #

现有ClientID字段格式统一为姓氏, 名字 (档案编号).，示例：Doe, John (W21135446).，需要拆分出三个字段：

• 姓氏（Last Name）
• 名字（First Name）
• 档案编号（File Number，移除前后括号和末尾的点）

实现代码 #

假设存储ClientID的表名为ClientTable，对应字段名为ClientID，代码如下：

SELECT
    -- 提取姓氏：截取逗号前的内容，去除两端空格
    LTRIM(RTRIM(SUBSTRING(ClientID, 1, CHARINDEX(',', ClientID) - 1))) AS [Last Name],
    -- 提取名字：截取逗号后、左括号前的内容，去除两端空格
    LTRIM(RTRIM(SUBSTRING(ClientID, CHARINDEX(',', ClientID) + 1, CHARINDEX('(', ClientID) - CHARINDEX(',', ClientID) - 1))) AS [First Name],
    -- 提取档案编号：截取左右括号之间的内容，去除两端空格
    LTRIM(RTRIM(SUBSTRING(ClientID, CHARINDEX('(', ClientID) + 1, CHARINDEX(')', ClientID) - CHARINDEX('(', ClientID) - 1))) AS [File Number]
FROM ClientTable
-- 过滤符合格式的记录，避免边界异常引发报错
WHERE CHARINDEX(',', ClientID) > 0 
  AND CHARINDEX('(', ClientID) > CHARINDEX(',', ClientID)
  AND CHARINDEX(')', ClientID) > CHARINDEX('(', ClientID)

测试用例 #

如果需要单条测试效果，可以直接用变量执行：

DECLARE @ClientID NVARCHAR(100) = 'Doe, John (W21135446).'
SELECT
    LTRIM(RTRIM(SUBSTRING(@ClientID, 1, CHARINDEX(',', @ClientID) - 1))) AS [Last Name],
    LTRIM(RTRIM(SUBSTRING(@ClientID, CHARINDEX(',', @ClientID) + 1, CHARINDEX('(', @ClientID) - CHARINDEX(',', @ClientID) - 1))) AS [First Name],
    LTRIM(RTRIM(SUBSTRING(@ClientID, CHARINDEX('(', @ClientID) + 1, CHARINDEX(')', @ClientID) - CHARINDEX('(', @ClientID) - 1))) AS [File Number]

执行后输出结果和要求完全匹配。

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