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

How can I make this select only the row with "1.4" if result has values "15.2","UNK","1.4"?

SELECT * FROM tbl WHERE result<4.0

(ideally something that works both for 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');

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

@ajo, if you want to select only rows with "1.4" if result has values "15.2","UNK","1.4", what about using Like??

SELECT * FROM tbl WHERE result like '1.4'

Does this work for you?

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

This works for SQL Server:

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