重写ISNUMERIC函数

declare @test table(strings varchar(50))

 

Insert into @test

 

Select '12d3' union all

 

Select '87234.45' union all

 

Select '$123,456.00' union all

 

Select '  12  ' union all

 

Select char(10) union all

 

Select '$'

 

 

 

select strings,isnumeric(strings)as valid from @test

 

/*

 

strings                                            valid

 

-------------------------------------------------- -----------

 

12d3                                               1

 

87234.45                                           1

 

$123,456.00                                        1

 

  12                                               1

 

 

 

                                                  1

 

$                                                  1

 

 

 

(6 行受影响)

 

 

 

*/

 

 

 

--重写ISNUMERIC函数

 

 

 

CREATE Function Is_numeric(@value varchar(25))

    Returns bit

as

Begin

    Return(

         case when @value not like '%[^-0-9.]%'

                   and len(@value)-len(replace(@value,'.',''))<2

                   and 1=(case when charindex('-',@value)>0

                                  then

                                    case when left(@value,1)='-'

                                           and len(@value)-len(replace(@value,'-',''))<2

                                           and len(@value)>1

                                         then 1 else 0

                                    end

                               else 1 end)

                 then 1 else 0 end)

 

End

 

GO

 

 

 

select strings,dbo.is_numeric(strings)as valid from @test

 

/*

 

strings                                            valid

 

-------------------------------------------------- -----

 

12d3                                               0

 

87234.45                                           1

 

$123,456.00                                        0

 

  12                                               0

 

 

 

                                                  0

 

$                                                  0

 

 

 

(6 行受影响)

 

 

 

*/