coolblue2000 coolblue2000 - 2 months ago 8
SQL Question

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?

Answer

CAST column if it is NUMERIC

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