Error converting data type varchar to float.

Hi,

I am experencing a data type cast issue in T-SQL, basically our application comtains both numeric and non-numeric data in the same column, I want to retrieve the numeric data only.

Assuming a table has one column

create table TT(c1 varchar(20) )

and insert 2 rows into the table

insert into TT values('100')

insert into TT values('test')

Now if I want to do numeric comparison on column c1, I will get an error, e.g.

select * from TT where c1 >100

because SQL server trying to convert 'test' to a number impcilitly.

I tried to create an UDF to handle the non-numeric data conversion, e.g. if the data is numeric then return the number, if the data is non-numeric, then return a NULL.

create function numcast(@.value VARCHAR) returns numeric as

begin

begin try

declare @.rtnval numeric;

set @.rtnval = cast(@.value as numeric);

return(@.rtnval);

end try

begin catch

return(NULL);

end catch;

end;

There are 2 problems in above function, 1. it still failed on cast() function, 2. T-SQL function does support try-catch structure.

Does anybody know what the best solution to handle this kind of issues?

Many thanks

try this

select * from TT
where ISNUMERIC(c1 + 'e0')=1
and c1 > 100

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Use the ISNUMERIC function to make sure the data is numeric before doing your comparison:

SELECT * FROM TT WHERE ISNUMERIC(c1) = 1 and c1 > 100

The query optimizer will only check the second part of the criteria (c1 > 100) if the first part is true. Note that if you flip the criteria, you will end up with a cast error again.|||

you can't just use ISNUMERIC, ISNUMERIC has a 'bug' isnumeric returns 1 for '1e4' for example

run the code below, that is why I use isnumeric( + 'e0')

declare @.v varchar(50)
select @.v ='1e4'
select isnumeric(@.v), isnumeric(@.v + 'e0')

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks for the info. I've never run into the problem using ISNUMERIC but sure enough your example does return the wrong info.

Is this 'bug' documented anywhere by Microsoft?|||

Not sure if it is documented anywhere but here is another one for you 1d2

declare @.v varchar(50)
select @.v ='1d2'
select isnumeric(@.v), isnumeric(@.v + 'e0')

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

The isnumeric issue is not a bug. 1e4 is a valid number in scientific notation as well as 1d2, etc. The function works as expected, you just have to be aware of what it considers a number.

There is a better way to test if it is a valid integer, but, I am running short of time at the moment, so, this is untested but similiar to what you need using regular expression matching something like:

select col1 from table
where col1 LIKE '[0-9][0-9][0-9]'

|||

>>The isnumeric issue is not a bug.<<

Agreed

>> 1e4 is a valid number in scientific notation as well as 1d2, etc.<<

True

>> The function works as expected<<

Definitely false. But that is a limitation of the function. It is WAY too open. Vote for this as an improvement:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=177308

I wholeheartedly agree that a parameter for type would help.

>>you just have to be aware of what it considers a number.<<

True again :)

|||

I agree with both of you that why I said 'bug' and not bug ;)

And yes IsInteger would be a nice addition to have as a function or as Louis suggested adding a parameter for type could also work

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

>>And yes IsInteger would be a nice addition to have as a function or as Louis suggested adding a parameter for type could also work<<

Technically I was repeating what the poster in Connect said, but yes, either would be nice. A function per type?

|||Thank you all for your help, the information is so helpful to me.|||

The value "$1" also passes the isnumeric test, even though it can only be converted to a "money" datatype.

The problem with using LIKE '[0-9][0-9][0-9]' is that it doesn't account for variable length numbers.

I definitely vote for one function per type: isinteger, isdecimal, ismoney, etc.

Of course nowadays we can at least do something like this:

declare @.x varchar(10), @.y int

set @.x = ' 12x'

begin try select @.y = convert(int, @.x) end try

begin catch end catch

Ron

|||you can use col1 like '%[0-9]%'|||

>>you can use col1 like '%[0-9]%'

No you can't take a look at this

create table blah(col1 varchar(40))
insert into blah values('100')
insert into blah values('100B')
insert into blah values('1A00')
insert into blah values('A100')

select * from blah where col1 like '%[0-9]%'

Denis the SQL Menace
http://sqlservercode.blogspot.com/