SQL Server中如何移除字段前导零及后缀与有效数字间的零？

针对你需要处理的两种字符串格式：

• 纯数字字符串：移除所有前导零（如00000123→123）
• 前缀含非数字字符、后跟零和有效数字的字符串：移除前缀与首个非零数字之间的所有零（如AA000345→AA345）

下面提供两种可行的SQL实现方案：

方案一：兼容全版本SQL Server #

使用STUFF+PATINDEX的组合逻辑，适配所有SQL Server版本，同时处理全零的特殊情况：

SELECT 
    CONCAT(
        -- 提取开头的非数字前缀（如AA000345中的AA）
        LEFT(target_col, PATINDEX('%[0-9]%', target_col) - 1),
        -- 处理数字部分：去掉前导零，全零则返回0
        CASE 
            WHEN SUBSTRING(target_col, PATINDEX('%[0-9]%', target_col), LEN(target_col)) LIKE '%0' 
                 AND SUBSTRING(target_col, PATINDEX('%[0-9]%', target_col), LEN(target_col)) NOT LIKE '%[^0]%' 
            THEN '0'
            ELSE STUFF(
                SUBSTRING(target_col, PATINDEX('%[0-9]%', target_col), LEN(target_col)),
                1,
                PATINDEX('%[^0]%', SUBSTRING(target_col, PATINDEX('%[0-9]%', target_col), LEN(target_col))) - 1,
                ''
            )
        END
    ) AS cleaned_col
FROM your_table;

逻辑说明 #

1. 先用LEFT+PATINDEX提取字符串开头的所有非数字字符，保留前缀
2. 提取从第一个数字开始的剩余部分，判断是否全为零：如果是则返回0，避免空值
3. 用STUFF结合PATINDEX移除剩余数字部分的所有前导零
4. 最后拼接前缀和处理后的数字部分得到结果

方案二：SQL Server 2017+ 简洁版 #

如果你的SQL Server版本在2017及以上，可以利用TRIM函数的指定修剪字符功能，代码更简洁：

SELECT 
    CONCAT(
        LEFT(target_col, PATINDEX('%[0-9]%', target_col) - 1),
        CASE 
            -- 处理全零的特殊情况
            WHEN TRIM('0' FROM SUBSTRING(target_col, PATINDEX('%[0-9]%', target_col), LEN(target_col))) = '' 
            THEN '0'
            ELSE TRIM('0' FROM LEADING SUBSTRING(target_col, PATINDEX('%[0-9]%', target_col), LEN(target_col)))
        END
    ) AS cleaned_col
FROM your_table;

逻辑说明 #

1. 同样先提取非数字前缀
2. 用TRIM('0' FROM LEADING ...)直接移除数字部分的前导零
3. 增加判断处理全零场景，避免返回空字符串

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