CODEWITHSUNDEEP
sqlsql-server
coolblue2000
coolblue2000

Reputation: 4948

Checking for numbers in char field

I am working on a database where some bright spark decided it was a good idea to store numbers and letters (for no good reason) in a char field.

Now I need to select where the number is below a certain value but obviously get an error due to the existence of letters in some of the records.

I have tried the following

select column1
from table1
where ISNUMERIC(column1)=1
AND column1<=16

however I get the following error

Conversion failed when converting the varchar value '5.00 ' to data type int.

Now that looks like a number to me so why can't it convert it?

Upvotes: 1

Views: 1054

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

This is a known issue in SQL Server. The correct solution in SQL Server 2012+ is to use try_convert():

select column1
from table1
where try_convert(int, column1) <= 16;

The problem with your query is that SQL Server (like all other databases) does not guarantee the order of evaluation of expressions in the where clause. Note that you cannot fix your version by using subqueries or CTEs, because SQL Server reserves the right to rearrange operations when compiling and executing the query.

In earlier versions, you can essentially do the same thing using a case:

select colum1
from table1
where (case when isnumeric(column1) = 0
            then 999
            else convert(int, column1)
       end) <= 16

Note: both of these assume that the string value is an integer. If it could be a decimal number, then use the appropriate type for conversion.

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

CAST column if it is NUMERIC

select column1
from table1
where ISNUMERIC(column1)=1
AND CAST(column1 AS DECIMAL(18,2)) <= 16.0

Upvotes: 1

Related Questions