SQL函数需求：移除字符串非数字字符并保留前导零

问题根源 #

你的ClearNonNumericCharacters函数本身是可以保留前导零的，测试结果丢失前导零是因为你把函数返回的字符串赋值给了INT类型变量@strTestNumeric——整数类型会自动忽略前导零，和函数无关。

直接执行函数就能看到它确实保留了前导零：

select dbo.ClearNonNumericCharacters('00098AB$709.89')

输出结果为：0009870989

修正测试代码 #

把存储结果的变量改为字符串类型即可：

declare @strTest VARCHAR(15) = null
declare @strTestNumeric NVARCHAR(MAX) = null -- 改为字符串类型，匹配函数返回值类型

select @strTest = '00098AB$709.89'
select @strTestNumeric = dbo.[ClearNonNumericCharacters](@strTest)

select @strTestNumeric -- 输出：0009870989

优化函数性能（可选） #

原函数用循环逐字符替换，处理长字符串时效率较低。可以改用以下两种写法提升性能：

版本1：基于集合的写法（适用于SQL Server 2017+）

alter function ClearNonNumericCharacters
    (@str nvarchar(max))
returns nvarchar(max)
as
begin
    declare @result nvarchar(max) = ''
    
    select @result += s.CharVal
    from (
        select substring(@str, n, 1) as CharVal
        from (select top (isnull(len(@str), 0)) row_number() over(order by (select null)) as n from sys.all_columns) t
    ) s
    where s.CharVal between '0' and '9'
    
    return @result
end

版本2：递归CTE写法（兼容SQL Server 2008+）

alter function ClearNonNumericCharacters
    (@str nvarchar(max))
returns nvarchar(max)
as
begin
    if @str is null or @str = '' return ''
    
    ;with CharProcessing as (
        select
            cast(case when substring(@str, 1, 1) between '0' and '9' then substring(@str, 1, 1) else '' end as nvarchar(max)) as CleanStr,
            1 as Position
        union all
        select
            CleanStr + case when substring(@str, Position + 1, 1) between '0' and '9' then substring(@str, Position + 1, 1) else '' end,
            Position + 1
        from CharProcessing
        where Position < len(@str)
    )
    select top 1 @str = CleanStr from CharProcessing order by Position desc
    
    return @str
end

内容的提问来源于stack exchange，提问作者CoolBreeze-Dev