ISNUMERIC was a function which was always thought to be unreliable due to various formats in which it accepts values. There are lot of values for which it will return true even when value may not be purely numeric.Refer http://beyondrelational.com/modules/2/blogs/70/posts/19819/understanding-how-isnumeric-function-works.aspx
But with the below discussed approach we can make it work based on our expectation to return only the valid numeric data.
Consider the below sample data for illustration
declare @t table
(
val varchar(20)
)
insert @t
values (‘123’),
(‘161.65.65.6’),
(‘756.76’),
(‘yrytrt656757’),
(‘987’),
(‘yfrytr’),
(‘7656@546’),
(‘wrew235235dsnfgngf’),
(‘213d3’)
Suppose if the requirement is to get valid integer values from it, then we can apply a logic like below for that
SELECT val
FROM @t
WHERE ISNUMERIC(val+’0.0E0′) =1
See the output below
SELECT val
FROM @t
WHERE ISNUMERIC(val+’E0′)=1
This will just check if its in valid scientific format and return the values
See output
This method is very cool and is much better than writing a series of conditions to account for all valid combinations to return valid numeric data.