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/
