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.

Consider 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 
                1 
            else 
                0 
            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