sqlserver数据库中隐藏字符遇到的坑

　　最近遇到了sqlserver数据库中隐藏字符串的坑，也是困扰我好久，最后来分享下我的解决办法。情况是这样的，需要统计分析某市的考试数据，统计每道题的平均分和得分率，一开始运行拆分统计SQL报错

后面多了很多的小数点，我把题号打印出来一看，确实是5没有错，然后使用 len(@id)函数打印出题号ID的长度，发现是16，看来是有隐藏字符了。

　　首先查看隐藏小数点的ASCII码为46

然后使用replace函数替换隐藏字符串，

set @id=replace(@id,char(46),'')

本来以为问题可以解决了，再次运行依旧报错，隐藏小数点没有替换掉，上网查了下其他人的解决办法，基本就是replace函数替换掉了，看来没有人遇到跟我一样的情况，这就有点尴尬了。

　　后面我想，既然替换不掉，那我只截取我需要的数字不就行了，于是我采用正则表达式的方法截取数字

set @id=stuff(@id,patindex('%[^0-9]%',@id),1,'')

　　这次没有报错，以为问题解决了，语句执行完之后去查看表数据，发现插入的全部是null，数字没有截取出来，返回去调试发现 patindex('%[^0-9]%',@id) 返回的是0，根本没有获取到数字的位置，这个5竟然不能识别为数字。

最后发现只有SUBSTRING(@id,1,1) 能截取出想要的数据，但是这个题号不是个位数的，还有两位数三位数的题号，并且后面的隐藏小数点的长度也不一样，我没有办法获取隐藏字符串出现的位置。经过一番折腾，我的最后解决方法就是循环截取每个字符串比对，下面上代码

CREATE function [dbo].[fn_getnumber](@str varchar(100))
returns varchar(100)
begin
    declare @return varchar(100) = ''
    declare @temp varchar(2)
    declare @IsDecimal int=0;
    declare @IsNumber int =0;
    --此方法解决含有小数点隐藏字符，转换成数字和小数失败的数据，
    
    while LEN(@str) > 0 
    begin
        set @temp = SUBSTRING(@str,1,1)
        if (@IsDecimal=1 and @temp='.' and @IsNumber=1)
        begin 
            --已经添加了小数点，后面是数字，则不再添加小数点返回数据
            break;
        end 
        if (@IsDecimal=1 and @temp='.' and @IsNumber=0)
        begin 
            --已经添加了小数点，后面还是小数点，则删除小数点返回数据
            select @return = SUBSTRING(@return,1,len(@return)-1)
            break;
        end 
        if (@temp between '0' and '9')
        begin
            select @return = @return + @temp
            if @IsDecimal=1
            set @IsNumber=1; --添加了小数点后面是数字，状态改为1
        end 
        set @str = SUBSTRING(@str,2,len(@str))
        --如果是小数点，并且后面还有数据
        if (@temp='.') and (LEN(@str) > 0 )
        begin
            select @return = @return + @temp
            set @IsDecimal=1; --增加了小数点将状态改为1
        end
    end
    return @return
end


GO

　　然后执行SQL的时候调用方法，返回需要的数字和小数

set @id=dbo.fn_getnumber(@id);

　　终于解决了隐藏小数点的问题，这就是我的最后解决方案，如果有更好的解决方案欢迎评论交流。最主要的是这个隐藏字符是个小数点，而且我转换成绩也是有小数点存在的，之前使用正则表达式的方式其实是不行的，毕竟小数的小数点还是要保存的。