I found this to work:

SELECT uri, evFieldUri, evFieldVal , CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV

WHERE URI > 0 AND uri IN

(

      SELECT URI FROM TSEXFIELDV WHERE (ISNUMERIC(evFieldVal)=1) AND evFieldUri IN

      ( SELECT URI FROM TSEXFIELD WHERE exFieldFormat IN (1,11))

);

It Returns:

uri           evFieldUri           evFieldVal           (No column name)

224016  3267       +000089243829 89243829

224019  2717       +000089243825 89243825

224472  3333       +000000000000000000000017     17

225052  3267       +000089243829 89243829

225055  2717       +000089243825 89243825

So, then I went back to:

SELECT uri, evFieldUri, evFieldVal , CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV

WHERE URI > 0

AND ISNUMERIC(evFieldVal)=1 AND uri IN

(

      SELECT URI FROM TSEXFIELDV WHERE evFieldUri IN

      ( SELECT URI FROM TSEXFIELD WHERE exFieldFormat IN (1,11))

);

And it returns this error:

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to bigint.

So, I tried again, and this worked

SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal AS BIGINT), ISNUMERIC(evFieldVal) FROM TSEXFIELDV WHERE URI > 0 AND evFieldUri IN ( SELECT URI FROM TSEXFIELD WHERE exFieldFormat IN (1,11))

Hmm, so I logged out and came back and tried again, and it still worked.

So then I tried

SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal AS BIGINT) FROM TSEXFIELDV

WHERE URI > 0

AND evFieldUri IN ( SELECT URI FROM TSEXFIELD WHERE exFieldFormat IN (1,11))

AND ISNUMERIC(evFieldVal) = 1

And it fails.

It's got me completely confused?

Rory

You need to understand how logically SQL Server produces a query

1 FROM

2 WHERE

3. GROUP BY 

4. having

5.SELECT

So, if you use ISNUMERIC() in SELECT sql server is able to eliminate none numeric values but in you put in  WHERE condition it does imply a certain order of executuon

You also need to look at execution plan of both queries, do you see any differences?

The WHERE clause was pulled out from the subquery and placed in the generated plan above the cCAST(evFieldVal AS BIGINT)  expression. During execution SQL Server is asked to convert the string value from the column evFieldVal to a BIGINT and, understandably, it fails.