Enhanced ISNUMERIC() function



It seems often users want to check whether the data has only numbers in a varchar type column. The commonly suggested one is to make use of ISNUMERIC() function. But the problem in using that function is that it will treat some alphabets, $, char(10),etc as numbers.

 

Cosider this example

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


The result is

strings                                                         valid      
-----------------------------------------------    --------

12d3                                                            1
87234.45                                                      1
$123,456.00                                                 1
  12                                                             1
                                                                   1
$                                                                 1

Note that only 87234.45 should be considered as numeric and not others

The following function will solve this problem

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 
               
            else 
               
            end
        else
                1
        end
    ) then
        1
    else
        0
    end
    )
End

Now execute this select statement

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

The result is

strings                                                         valid
-----------------------------------------------      -----
12d3                                                             0
87234.45                                                      1
$123,456.00                                                  0
  12                                                              0
                                                                    0
$                                                                  0


Published 27 August 2007 16:23 by Madhivanan
Filed under: ,

Comments

No Comments