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 SQL Server's ISNUMERIC() function. But the problem in using that function is that it will treat some alphabets, $, char(10),etc as numbers.
Consider this example (Run this in query analyser)
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