SQL Syntax Error using IsNumeric on Varchar Column

Question

I am trying to execute a SQL-Statement

SELECT * 
FROM Menu
WHERE ISNumeric(Data) = 1
AND Data = 296

where the Data field may contain numeric or non-numeric contents. But I am getting syntax error something like

Syntax error on converting the varchar-value '/home' in a column of Data type int.

Answer 1

since DATA column is varchar, you need to enclosed the value (296) with single quote

SELECT * 
FROM   Menu
WHERE  ISNumeric(Data) = 1 AND Data = '296'

you can still directly query without using ISNUMERIC here, and increases performance since you don;t have to check every record for numeric values.

Using IsNumeric is pointless, I guess, in your situation.

SELECT * 
FROM   Menu
WHERE  Data = '296'

Answer 2

Unfortunately, SQL doesn't guarantee short circuit evaluation, therefore the IsNumeric(Data) filter won't prevent evaluation of the second predicate, Data = 296, if the first condition is false.

What you can do however is leave the comparison as a char literal, e.g.

SELECT * 
FROM Menu
WHERE ISNumeric(Data) = 1
AND Data = '296';

Out of interest, why would you need to do the IsNumeric check if you are comparing to an exact numeric value?

Answer 3

Just to add to @StuartLC's answer, one workaround for this is by using a subquery to obtain all the numeric values first, e.g.:

SELECT *
FROM   (
    SELECT CAST(Data AS int) AS Data, 
           -- Other fields
    FROM   Menu
    WHERE  ISNumeric(Data) = 1
) subquery
WHERE subquery.Data = 296

Although note you will need to be sure you won't have decimal numbers, or scientific notation, or that sort of thing, as IsNumeric will return 1 for these. This question will help you resolve issues around that...