Thanks to Erland I learned a cool way to validate integer or decimal values from varchar field having intermixed data which I’m sharing through this blog.
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.
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
The technique is to make it in scientific format and check if decimal portion is 0.
Similarly to check for decimal format from above we can write condition as 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.
