Thursday, March 13, 2014

Checking for Integer or Decimal values using ISNUMERIC

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.

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.