在SQL Server 2012受限环境中实现InitCap/Proper功能

环境与限制 #

• 使用版本：

Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64) - Nov 1 2020 00:48:37   
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

• 限制：
  • 无法使用STRING_SPLIT等SQL Server 2016+新增函数
  • 无权执行CREATE命令创建自定义函数、存储过程等

需求目标 #

1. 实现字符串首字母大写、其余小写格式化，数字开头的单词需将后续首字母大写（如234name→234Name）
2. 可选支持撇号、"Mc"前缀等特殊单词格式（如MCDONALD→McDonald）
3. 支持独立单词例外替换（如独立的LLC→Llc）

实现方案 #

所有方案均直接嵌入SELECT语句，无需创建任何数据库对象。

1. 核心功能实现（基础首字母大写） #

利用递归CTE逐字符处理字符串，识别分隔符后自动大写下一个字符，同时处理数字开头的单词：

SELECT
    NumericField1 AS "Number Field",
    (
        WITH RecursiveCTE AS (
            -- 初始化：处理字符串前两个字符，拆分剩余部分
            SELECT
                1 AS Position,
                UPPER(LEFT(LTRIM(RTRIM(StringField1)), 1)) + LOWER(SUBSTRING(LTRIM(RTRIM(StringField1)), 2, 1)) AS CurrentString,
                SUBSTRING(LTRIM(RTRIM(StringField1)), 3, LEN(LTRIM(RTRIM(StringField1)))) AS RemainingString
            WHERE LEN(LTRIM(RTRIM(StringField1))) > 0
            UNION ALL
            -- 递归处理剩余字符
            SELECT
                Position + 1,
                CurrentString + 
                CASE
                    -- 遇到空格、撇号、短横线等分隔符，大写下一个字符
                    WHEN SUBSTRING(RemainingString, 1, 1) IN (' ', '''', '-') THEN 
                        SUBSTRING(RemainingString, 1, 1) + UPPER(SUBSTRING(RemainingString, 2, 1))
                    -- 前一个字符是数字，且当前字符不是分隔符，大写当前字符
                    WHEN ISNUMERIC(SUBSTRING(CurrentString, LEN(CurrentString), 1)) = 1 AND SUBSTRING(RemainingString, 1, 1) <> ' ' THEN
                        UPPER(SUBSTRING(RemainingString, 1, 1))
                    -- 其余情况转为小写
                    ELSE
                        LOWER(SUBSTRING(RemainingString, 1, 1))
                END,
                SUBSTRING(RemainingString, 2, LEN(RemainingString))
            FROM RecursiveCTE
            WHERE LEN(RemainingString) > 0
        )
        -- 取递归处理后的最终结果
        SELECT TOP 1 CurrentString
        FROM RecursiveCTE
        ORDER BY Position DESC
    ) AS "Customer Name"
FROM 
    DataBaseTable

2. 可选：添加"Mc"等前缀例外 #

在递归逻辑中加入对特定前缀的判断，处理MCDONALD这类单词转为McDonald：

SELECT
    NumericField1 AS "Number Field",
    (
        WITH RecursiveCTE AS (
            -- 初始化：判断是否以Mc开头并处理
            SELECT
                1 AS Position,
                CASE
                    WHEN LOWER(LEFT(LTRIM(RTRIM(StringField1)), 2)) = 'mc' THEN
                        'Mc' + UPPER(SUBSTRING(LTRIM(RTRIM(StringField1)), 3, 1)) + LOWER(SUBSTRING(LTRIM(RTRIM(StringField1)), 4, LEN(LTRIM(RTRIM(StringField1)))))
                    ELSE
                        UPPER(LEFT(LTRIM(RTRIM(StringField1)), 1)) + LOWER(SUBSTRING(LTRIM(RTRIM(StringField1)), 2, LEN(LTRIM(RTRIM(StringField1)))))
                END AS CurrentString,
                -- 根据前缀调整剩余字符串起点
                CASE
                    WHEN LOWER(LEFT(LTRIM(RTRIM(StringField1)), 2)) = 'mc' THEN
                        SUBSTRING(LTRIM(RTRIM(StringField1)), 4, LEN(LTRIM(RTRIM(StringField1))))
                    ELSE
                        SUBSTRING(LTRIM(RTRIM(StringField1)), 3, LEN(LTRIM(RTRIM(StringField1))))
                END AS RemainingString
            WHERE LEN(LTRIM(RTRIM(StringField1))) > 0
            UNION ALL
            -- 递归处理：识别分隔符后的Mc前缀
            SELECT
                Position + 1,
                CurrentString + 
                CASE
                    WHEN SUBSTRING(RemainingString, 1, 2) IN ('mc', 'MC') THEN
                        'Mc' + UPPER(SUBSTRING(RemainingString, 3, 1)) + LOWER(SUBSTRING(RemainingString, 4, 1))
                    WHEN SUBSTRING(RemainingString, 1, 1) IN (' ', '''', '-') THEN 
                        SUBSTRING(RemainingString, 1, 1) + UPPER(SUBSTRING(RemainingString, 2, 1))
                    WHEN ISNUMERIC(SUBSTRING(CurrentString, LEN(CurrentString), 1)) = 1 AND SUBSTRING(RemainingString, 1, 1) <> ' ' THEN
                        UPPER(SUBSTRING(RemainingString, 1, 1))
                    ELSE
                        LOWER(SUBSTRING(RemainingString, 1, 1))
                END,
                -- 处理Mc前缀后调整剩余字符串
                CASE
                    WHEN SUBSTRING(RemainingString, 1, 2) IN ('mc', 'MC') THEN
                        SUBSTRING(RemainingString, 4, LEN(RemainingString))
                    ELSE
                        SUBSTRING(RemainingString, 2, LEN(RemainingString))
                END
            FROM RecursiveCTE
            WHERE LEN(RemainingString) > 0
        )
        SELECT TOP 1 CurrentString
        FROM RecursiveCTE
        ORDER BY Position DESC
    ) AS "Customer Name"
FROM 
    DataBaseTable

3. 锦上添花：独立单词例外替换 #

在基础格式化后，添加独立单词的替换逻辑（如LLC→Llc），可通过扩展REPLACE链添加更多例外：

SELECT
    NumericField1 AS "Number Field",
    (
        -- 先执行基础格式化（含Mc前缀处理）
        WITH FormattedCTE AS (
            WITH RecursiveCTE AS (
                SELECT
                    1 AS Position,
                    CASE
                        WHEN LOWER(LEFT(LTRIM(RTRIM(StringField1)), 2)) = 'mc' THEN
                            'Mc' + UPPER(SUBSTRING(LTRIM(RTRIM(StringField1)), 3, 1)) + LOWER(SUBSTRING(LTRIM(RTRIM(StringField1)), 4, LEN(LTRIM(RTRIM(StringField1)))))
                        ELSE
                            UPPER(LEFT(LTRIM(RTRIM(StringField1)), 1)) + LOWER(SUBSTRING(LTRIM(RTRIM(StringField1)), 2, LEN(LTRIM(RTRIM(StringField1)))))
                    END AS CurrentString,
                    CASE
                        WHEN LOWER(LEFT(LTRIM(RTRIM(StringField1)), 2)) = 'mc' THEN
                            SUBSTRING(LTRIM(RTRIM(StringField1)), 4, LEN(LTRIM(RTRIM(StringField1))))
                        ELSE
                            SUBSTRING(LTRIM(RTRIM(StringField1)), 3, LEN(LTRIM(RTRIM(StringField1))))
                    END AS RemainingString
                WHERE LEN(LTRIM(RTRIM(StringField1))) > 0
                UNION ALL
                SELECT
                    Position + 1,
                    CurrentString + 
                    CASE
                        WHEN SUBSTRING(RemainingString, 1, 2) IN ('mc', 'MC') THEN
                            'Mc' + UPPER(SUBSTRING(RemainingString, 3, 1)) + LOWER(SUBSTRING(RemainingString, 4, 1))
                        WHEN SUBSTRING(RemainingString, 1, 1) IN (' ', '''', '-') THEN 
                            SUBSTRING(RemainingString, 1, 1) + UPPER(SUBSTRING(RemainingString, 2, 1))
                        WHEN ISNUMERIC(SUBSTRING(CurrentString, LEN(CurrentString), 1)) = 1 AND SUBSTRING(RemainingString, 1, 1) <> ' ' THEN
                            UPPER(SUBSTRING(RemainingString, 1, 1))
                        ELSE
                            LOWER(SUBSTRING(RemainingString, 1, 1))
                    END,
                    CASE
                        WHEN SUBSTRING(RemainingString, 1, 2) IN ('mc', 'MC') THEN
                            SUBSTRING(RemainingString, 4, LEN(RemainingString))
                        ELSE
                            SUBSTRING(RemainingString, 2, LEN(RemainingString))
                    END
                FROM RecursiveCTE
                WHERE LEN(RemainingString) > 0
            )
            SELECT TOP 1 CurrentString AS FormattedText
            FROM RecursiveCTE
            ORDER BY Position DESC
        )
        -- 替换独立单词例外，可添加更多REPLACE语句扩展
        SELECT REPLACE(REPLACE(FormattedText, ' Llc ', ' LLC '), ' Llc', ' LLC') FROM FormattedCTE
    ) AS "Customer Name"
FROM 
    DataBaseTable

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