Wednesday, April 16, 2008

Problem Numeric check in SQL Server

In my datase i had a table which contains both numeric and characters and the table datatype was Varchar, and i have to find the min of the of the numeric value. In sql server the function ISNUMERIC(expr) was the solution to get rid of the character value.
The ISNUMERIC function determines whether an expression is a valid numeric type. returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. So i used the query like below
Select min(ColName) from demo where ISNUMERIC(ColName)=1
And i was getting my desired value.
But one day it started an exception in my asp.net page. And i could not find the reason.
After a long debugging i found that it was the problem with ISNUMERIC function of sql server. The ISNUMERIC function is returning 1 for some non-numeric character like '-' i.e
Select Isnumeric('-') returns 1 not 0.
And it was returning '-' from the query and error in asp.net page.
Then i tried with casting the result to int as.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1
And in this way the error was solved but the min value was always 0 as Cast('-')=0
Then i identified this abnormal characters as excluded them through the where clause.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1 and eadr not in('+','-','$').
This behaviour occurs for these three charactes('+','-','$') i have found so far.
For more information check this link

2 comments:

Júlio Melo said...
This comment has been removed by the author.
Júlio Melo said...

It is worse than you thought.

Try SELECT ISNUMERIC(CHAR(13)) (I tried on SQL Server 2000). But if you try to cast it, you will get an error.