SQL ISNUMERIC is returning odd results

Question

So I have this query:

SELECT 
    *
FROM
    (SELECT * FROM PE_ProductInstance WHERE ISNUMERIC(SerialStart) = 1) p
where 
    CAST(SerialStart AS int) < 8174

The subquery SHOULD, in theory, filter out any results where the column SerialStart is not numeric. And indeed, when I look at the results of just:

 SELECT * FROM PE_ProductInstance WHERE ISNUMERIC(SerialStart) = 1

All of the results are indeed numeric for column SerialStart. So I don't understand why that query results in this error:

Conversion failed when converting the nvarchar value '8A0357' to data type int.

Also what's weird is this query returns nothing:

SELECT
    *
FROM
    (SELECT * FROM PE_ProductInstance WHERE ISNUMERIC(SerialStart) = 1) p
where 
    SerialStart  = '8A0357'

What am I doing wrong here?

Answer 1

Try_Cast is the right method to do this. See this code:

DECLARE @table TABLE( 
    id int identity(1,1),
    name varchar(100));

INSERT into @table
VALUES
('1111'),('8A0357');

SELECT *
FROM @table p
WHERE TRY_CAST(p.Name AS int) < 8174;