Perform math in column with INT and non-INT characters

[bmacbmac]

Hi. I have two varchar columns (MAX and MIN) with INT and non-INT characters. Some have leading/trailing spaces. I am trying to do math with only the INT rows using the following query:

select convert(int, max) - convert(int, min)
from landranges
where (ISNUMERIC(ltrim(rtrim(min)) + 'e0')=1   and ISNUMERIC(ltrim(rtrim(max)) + 'e0')=1)
and convert(int, max) - convert(int, min) > 50

But I still get errors like:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'F ' to data type int.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '8210.8640 ' to data type int.


Is there a way I can pick just the INT rows to run the math against?

 

[gmmastros]

Try this:

select  Case When (ISNUMERIC(ltrim(rtrim(min)) + 'e0')=1   and ISNUMERIC(ltrim(rtrim(max)) + 'e0')=1)
             Then convert(int, max) - convert(int, min)
			 Else NULL
			 End
From  ......

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

Wow... 1st using a column name with MIN and MAX is risky since these are functions. I am surprised that they did not get flagged as reserved words.

In any event, sounds like ROUND() may solve issues after converting to REAL...

DECLARE @TESTVAR NVARCHAR(10)
SET @TESTVAR= '8210.8640 '
--PRINT CONVERT(INT,@TESTVAR)
--Msg 245, Level 16, State 1, Line 3
--Conversion failed when converting the nvarchar value '8210.8640 ' to data type int.

PRINT ROUND(CONVERT(REAL,@TESTVAR),0)
--8211

Hope This Was Helpful,

Steve



Steve Medvid
IT Consultant & Web Master

 

[bmacbmac]

Thanks gmmastros! It worked to get rid of my error for rows containing F, but still getting the error with a '.' (which i actually just avoided by adding in: and min not like '%.%')


Smedvid - I'll try using the 'round' option next. My column names aren't really min/max... they are much longer but shortened for this example. I never really thought about them being functions so I will avoid in the future.

Thanks!