August 28, 2009 at 8:38 am
I have encountered some odd behavior in SQL Server 2005 which is messing with my ability to get some new views working.
What I see is that a CAST within the select list for a query appears to be evaluated for rows that should be excluded by the WHERE clause of the statement.
A simplified example of my problem:
create table test1
(key1 int,
column1 varchar(10)
)
insert into test1 (key1, column1)
select 1, 'GC435'
union
select 1, '23456'
union
select 2, '2344'
select key1, column1, cast(column1 as int) as cst from test1
where (isnumeric(column1) = 0)
Running this code causes the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'GC435' to data type int.
And to my thinking, the offending non-numeric value should have been excluded from the result set via the WHERE clause prior to the CASTing operation.
Without using a temp table and splitting this type of action into steps, anybody know of a work-around to force SQL server to complete it's WHERE filtering before performing other conversions embedded in the result set?
Tnx
August 28, 2009 at 8:40 am
try evaluating isnumeric = 1 not 0
select key1, column1, cast(column1 as int) as cst from test1
where (isnumeric(column1) = 1)
August 28, 2009 at 8:49 am
Well, thanks, but as I mentioned, this is greatly simplified and the real WHERE clause is significantly more involved. I picked this one simply to illustrate what I think the issue is.
August 28, 2009 at 8:50 am
And be aware that ISNUMERIC checks whether the value can be cast to one of the numeric data types, not whether it can be case to int.
SELECT ISNUMERIC ('1.245d10')
SELECT CAST('1.245d10' AS INT)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2009 at 8:53 am
gary.wood (8/28/2009)
Well, thanks, but as I mentioned, this is greatly simplified and the real WHERE clause is significantly more involved. I picked this one simply to illustrate what I think the issue is.
Can you post the full, unsimplified query? The problem with the piece that you posted is that the isnumeric check is the wrong way around, hard to infer or guess the problem in the full thing from that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2009 at 9:07 am
The WHERE criteria (current iteration) attempts to limit the result set to values that match this pattern:
A999A
Where "A" is an alpha, and "9" is a numeric. The actual length of the column value can be longer than the pattern (up to 10), but the intent is to pick up values with a single Alpha prefix or Suffix, and no embedded blanks.
The table contains street addreses, which have been historically open to freeform input and were blank padded - just to explain some of the odd bits of the criteria. The data is admittedly horrid 😮
where
( (isnumeric(housestart) = 0 and m.housestart LIKE '_%[0-9]%_')
and
(charindex(' ', RTRIM(housestart)) = 0)
and
(
isnumeric(SUBSTRING(housestart,2, datalength(rtrim(housestart)) - 1)) = 1
or
isnumeric(SUBSTRING(housestart,1, datalength(rtrim(housestart)) - 1)) = 1
)
)
August 28, 2009 at 9:10 am
Understood. I wish tSql had a native IsInteger...
But either way, a value like 'NG123' would fail the ISNUMERIC test, so I expected it to be filtered before the CAST was performed.
August 28, 2009 at 9:22 am
gary.wood (8/28/2009)
Understood. I wish tSql had a native IsInteger...But either way, a value like 'NG123' would fail the ISNUMERIC test, so I expected it to be filtered before the CAST was performed.
'NG123' will be filtered in the Where condition, there must be a problem with the logic in the Where condition, are you able to post the entire query and the results you are tryin to acheive to avoid further guess work..?
August 28, 2009 at 9:25 am
My apologies. The example I used is bogus and I somehow reversed my logic. The problem I encountered appeared once I had created views from queries I had worked on.
I threw together a simple example that generated the same error message, but in looking at it again, it is obviously asking for the wrong data.
I will try to formulate a better example that is still simple. I appreciate the help regardless.
August 28, 2009 at 5:17 pm
It would be helpful to see the full WHERE clause.
Given the 'A999A' pattern, I assume it contains something like this?
WHERE someCol like '[A-Z][0-9][0-9][0-9][0-9][A-Z]'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2009 at 1:59 am
In your original posting, I'd say it's the SELECT that's failing even before the WHERE is applied.
If you remove the third item in the SELECT it gives you back the columns with alphanumeric data.
select key1, column1 --, cast(column1 as int) as cst
from test1
where (isnumeric(column1) = 0)
Otherwise, as steveb has already pointed out, where (isnumeric(column1) = 1) returns the rows with only numeric data in column1.
select key1, column1, cast(column1 as int) as cst
from test1
where (isnumeric(column1) = 1)
To echo the others, please provide more details so that we can understand the pattern matching business.
Riz
August 29, 2009 at 12:36 pm
Riz Gulamhussein (8/29/2009)
In your original posting, I'd say it's the SELECT that's failing even before the WHERE is applied.
Nope. SQL evaluates the WHERE clause before it evaluates the SELECT clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 30, 2009 at 8:16 pm
You can't rely on a where clause to stop these types of errors. This will happen quite commonly. Where's are not necessarily evaluated before the select, the only thing you can *force* it to evaluate in proper order is a case statement.
Try using something like CASE WHEN ISNUMERIC(column1) = 1 THEN CAST(column1as int) END
This will only attempt to cast it when it can cast it. (Although as Gail already pointed out, numeric doesn't necessarily mean int)
August 30, 2009 at 9:45 pm
While we're on the subject, ISNUMERIC() doesn't even truly mean NUMERIC.
declare @testString varchar(10)
set @testString = '.'
select @testString as [@testString], isnumeric(@testString) as [IsNumeric]
set @testString = ',,'
select @testString as [@testString], isnumeric(@testString) as [IsNumeric]
set @testString = '-'
select @testString as [@testString], isnumeric(@testString) as [IsNumeric]
set @testString = '$'
select @testString as [@testString], isnumeric(@testString) as [IsNumeric]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply