SQL: "... Result WHERE <4.0" if "result": "15.2", "UNK", "1.4"?

Question

How can I do this, select only the line with "1.4" if the result has the values ​​"15.2", "UNK", "1.4"?

 SELECT * FROM tbl WHERE result<4.0 

(ideally this works for both MSSQL and MySQL ...)

Here is the result column

 CREATE TABLE IF NOT EXISTS `tbl` ( `result` varchar(5) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `tbl` (`result`) VALUES ('15.2'),('UNK'),('1.4'); 

Answer 1

 select * from tbl where 1 = case ISNUMERIC(result + 'e0') when 1 then case when CAST(result as float) < 4.0 then 1 else 0 end when 0 then 0 end 

Answer 2

@ajo, if you want to select only lines with "1.4", if the result has the values ​​"15.2", "UNK", "1.4", how about using Like ??

SELECT * FROM tbl WHERE result like '1.4'

Answer 3

Does this work for you?

 SELECT * FROM table WHERE ISNUMERIC(result) = 1 AND result < 4; 

Answer 4

This works for SQL Server:

 SELECT * FROM tbl WHERE CAST(NULLIF(result, 'UNK') AS DECIMAL(9, 4)) < 4.0;