SQL如何将全大写姓名转为正常大小写并将CTE结果传入自定义函数

我有一张存储员工信息的SQL表，其中员工姓名全部为全大写格式（例如SMITH-EASTMAN,JIM M）。我已经成功将全名拆分为「姓氏」和「名字」两个独立列，现在需要把全大写内容转换为正常大小写格式，同时想知道如何将公用表表达式（CTE）的执行结果传入自定义函数中处理。

已实现的CTE代码 #

WITH CTE AS
(
    SELECT FullName = [Employee Name],
           LastName = SUBSTRING([Employee Name], 1, CHARINDEX(',',[Employee Name])-1),             
           FirstNameStartPos = CHARINDEX(',',[Employee Name]) + 1,
           MidlleInitialOrFirstNameStartPos = CHARINDEX(' ',[Employee Name]),
           MiddleInitialOrSecondFirstName = SUBSTRING([Employee Name], CHARINDEX(' ',[Employee Name]),LEN([Employee Name])),
           MiddleInitialOrSecondFirstNameLen = LEN(SUBSTRING([Employee Name], CHARINDEX(' ',[Employee Name]),LEN([Employee Name]))) - 1       
    FROM ['Med-PS PCN Mapping$']    
    WHERE [PS Employee ID] IS NOT NULL  
),
CTE2 AS
(
    SELECT FullName = CTE.FullName,
           DerivedFirstName = CASE
                                WHEN CTE.MiddleInitialOrSecondFirstNameLen = 1 
                                  THEN SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.MidlleInitialOrFirstNameStartPos - CTE.FirstNameStartPos)
                                ELSE SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.FirstNameStartPos + CTE.MiddleInitialOrSecondFirstNameLen)
                              END,
           DerivedLastName = CTE.LastName                         
    FROM CTE
)
SELECT * 
FROM CTE2

运行结果 #

已定义的大小写转换自定义函数 #

CREATE FUNCTION [dbo].[FixCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END
GO

1. 修复CTE2中名字截取的错误 #

当前CTE2的ELSE分支SUBSTRING第三个参数计算错误，SUBSTRING的第三个参数是截取长度而非结束位置，原逻辑会导致MADISON MARIE被截断为MADISON MARI，修正后的CTE2代码如下：

CTE2 AS
(
    SELECT FullName = CTE.FullName,
           DerivedFirstName = CASE
                                WHEN CTE.MiddleInitialOrSecondFirstNameLen = 1 
                                  THEN SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.MidlleInitialOrFirstNameStartPos - CTE.FirstNameStartPos)
                                ELSE SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.MiddleInitialOrSecondFirstNameLen)
                              END,
           DerivedLastName = CTE.LastName                         
    FROM CTE
)

2. 直接在最终查询中调用自定义函数 #

CTE的结果可以直接作为参数传入自定义函数，只需要在查询CTE2时把对应字段传入FixCap即可，完整查询代码如下：

WITH CTE AS
(
    SELECT FullName = [Employee Name],
           LastName = SUBSTRING([Employee Name], 1, CHARINDEX(',',[Employee Name])-1),             
           FirstNameStartPos = CHARINDEX(',',[Employee Name]) + 1,
           MidlleInitialOrFirstNameStartPos = CHARINDEX(' ',[Employee Name]),
           MiddleInitialOrSecondFirstName = SUBSTRING([Employee Name], CHARINDEX(' ',[Employee Name]),LEN([Employee Name])),
           MiddleInitialOrSecondFirstNameLen = LEN(SUBSTRING([Employee Name], CHARINDEX(' ',[Employee Name]),LEN([Employee Name]))) - 1       
    FROM ['Med-PS PCN Mapping$']    
    WHERE [PS Employee ID] IS NOT NULL  
),
CTE2 AS
(
    SELECT FullName = CTE.FullName,
           DerivedFirstName = CASE
                                WHEN CTE.MiddleInitialOrSecondFirstNameLen = 1 
                                  THEN SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.MidlleInitialOrFirstNameStartPos - CTE.FirstNameStartPos)
                                ELSE SUBSTRING(CTE.FullName, CTE.FirstNameStartPos, CTE.MiddleInitialOrSecondFirstNameLen)
                              END,
           DerivedLastName = CTE.LastName                         
    FROM CTE
)
SELECT 
    FullName,
    [dbo].[FixCap](DerivedFirstName) AS FormattedFirstName,
    [dbo].[FixCap](DerivedLastName) AS FormattedLastName
FROM CTE2

执行效果示例 #

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