ISNUMERIC Question

Question

ISNUMERIC Question

markswick

SSC Enthusiast

Points: 156
More actions
April 8, 2011 at 11:38 am

#234677

In SQL 2000, the following pseudo-code worked without issue...
select distinct field1, field2
from
TABLE1 a
where
a.field2 <> ''
and exists (select 's'
from TABLE1 cust
join TABLE2 z
on cust.field1 = z.field3
and z.field4 in (<list>)
join TABLE3 y
on z.field5 = y.field6
where a.field2 = cust.field2
and isnumeric(y.field8) = 1
and charindex('.', y.field8) = 0
and convert(int, y.field8) >= 7
and convert(int, y.field8) <> 100
)
The conversion to int for "field8" worked because various alpha detritus was excluded via the first two pieces of criteria related to "field8".
However, the same code in 2008 R2, invoked an error consistently because the value '. ' was allowed through to the conversion. The only way that I could get this to work was to prep the data in a separate query and drop those results into a temp table. Does anyone know why 2008 R2 is not analyzing the criteria left to right? i.e. It appears the parser looks at the criteria as a whole. Any help in understanding this would be appreciated.
Sorry for not including more meaningful table names and fields. It's a Sarbanes/Oxley thing.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

It has to do with the optimizer and how much "smarter" it has gotten since 2000. In your situation however you are experiencing a regression as a result of improvements that help the majority of queries. Here is a working, albeit boiled down, version of your initial query that demonstrates how you can rewrite your query using a CASE expression to force the optimizer to evaluate your logical tests in order:

--SELECT ISNUMERIC('.') ;

IF OBJECT_ID(N'tempdb..#tmp') > 0

DROP TABLE #tmp ;

GO

CREATE TABLE #tmp (field8 VARCHAR(10)) ;

INSERT INTO #tmp

(

field8

)

SELECT '1'

UNION ALL

SELECT '2'

UNION ALL

SELECT '.'

UNION ALL

SELECT '100'

UNION ALL

SELECT '99'

UNION ALL

SELECT '0'

UNION ALL

SELECT 'abcdef' ;

WITH cte(field)

AS (SELECT CONVERT(INT, CASE WHEN ISNUMERIC(field8) = 1

AND CHARINDEX('.', field8) = 0 THEN field8

ELSE NULL

END)

FROM #tmp

)

SELECT *

FROM cte

WHERE field >= 7

AND field <> 100 ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

markswick SSC Enthusiast Points: 156 More actions · Answer 2

Interesting...

I've just recently been told that the ISNUMERIC function was modified to allow anything that might be related to money formatting will be viewed as a number. Therefore, that would explain why ISNUMERIC allows ' . ', ' - ', and ' / ' through.

Thanks for the response. I'm going to try out your code.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

ISNUMERIC was not changed from 2000 to 2005 with respect to a single decimal point. This returns 1 on 2000 and 2005:

SELECT ISNUMERIC('.') ;

which happens in support of the money-number data types. The function was named ISNUMERIC so the assumption that it means ISWHOLENUMBER or ISINTEGER is a mistake I see a lot of people make.

This CAST results in $0 on both 2000 and 2005:

DECLARE @x MONEY ;

SET @x = CAST('.' AS MONEY) ;

SELECT @x ;

This also returns 1 on both:

SELECT ISNUMERIC('1e0012');

because it is a valid representation of a floating point number expressed using exponential notation.

I just threw out a few points...there are a ton of comprehensive articles on the google about the limitations and behavior of ISNUMERIC.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

IsNumeric is something I suggest people do NOT use. I'm not sure if this helps, but I was having a discussion with a co-worker the other day and put this together to show how ISNUMERIC may not produce the result you might expect (and I'm sure there are more examples out there):DECLARE @Foo TABLE (Val VARCHAR(50))

INSERT @Foo (Val)

VALUES

('1')

,('1,000')

,('5e3')

,('100')

,('100.00')

,('9781297')

,('9781e297')

,('978w1297')

,('asdfg')

,('.9781297')

,('12d3')

,('$123,456.00')

,(' 12 ')

,(char(10))

,('$')

,(NULL)

SELECT

Val

,ISNUMERIC(Val) AS IsValNumeric

,CASE WHEN Val NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsInt

FROM @Foo

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Following OPC's and Lamprey's lead on this, please see the following article which explains a bit about ISNUMERIC...

http://www.sqlservercentral.com/articles/IsNumeric/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Use a case expression to filter the results; evaluation of the case conditions ends with the first true condition and avoids the conversion errors.

declare @Tmp table (field8 varchar(20)) ;

insert into @Tmp

select '1' union all

select '2' union all

select '.' union all

select '101'union all

select '100'union all

select '99'union all

select '6' union all

select '7' union all

select '8' union all

select 'abcdef'

select

*

from

@Tmp

where

case

when field8 like '%[^0-9]%'

then 0

when field8 >= 7 and field8 <> 100

then 1 else 0 end = 1

Results:

field8

--------------------

101

99

7

8