CASE STATEMENT TO WHERE CLAUSE

I have a case statement,and i want to write seperate where clauses for each of them HIGH LOW MEDIUM and NONE.

 CASE
                 WHEN (ISNUMERIC(REPLACE(ldd.Value, '%', '')) = 0) 
                                  THEN 'NONE'                    
                 WHEN (CONVERT(FLOAT,REPLACE(ldd.Value, '%', '')) > 9.0)
                                   THEN 'HIGH'                   
                 WHEN (CONVERT(FLOAT,REPLACE(ldd.Value, '%', '')) < 7.0 )
                                    THEN 'LOW'      
                 WHEN (CONVERT(FLOAT,REPLACE(ldd.Value, '%','')) BETWEEN 7.0 AND 9.0  )
                                   THEN 'MEDIUM'                    
                 WHEN (ISNULL(ldd.Value,'') = '')      
                                THEN 'NONE'    
                 END

when i try writing the where clause for NONE

    SELECT class
           ,rollno
           ,Value
    FROM STUDENT
    WHERE (ISNUMERIC(REPLACE(ldd.Value, '%', '')) = 0) 

Error MSg:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

When i write for the high

 SELECT class
               ,rollno
               ,Value
        FROM STUDENT
        WHERE (CONVERT(FLOAT,REPLACE(ldd.Value, '%', '')) > 9.0)

Error MSg:Error converting data type varchar to float.

what can be done to get only the records for high low or none while writn开发者_如何学编程g for where clause? THE VALUE FIELD IS VARCHAR

---

The order of evaluation for the CASE statement prevents you from ever calling CONVERT() on a varchar that can't be converted. You are not guaranteed to have this protection with a WHERE condition.

---

This is going to depend entirely on what is in the Value column. I am assuming they are something similar to AYP or other standardized testing where values are typically reported as xx.x%. In that case, something like this would work:

SELECT
    s.Value
    ,CASE WHEN REPLACE(s.Value, '%', '') BETWEEN '0.1' AND '7.0' THEN 'LOW'   
          WHEN REPLACE(s.Value, '%','') BETWEEN '7.0' AND '9.0' THEN 'MEDIUM'  
          WHEN REPLACE(s.Value, '%', '') BETWEEN '9.0' AND '100.0' THEN 'HIGH'   
     ELSE 'NONE'
     END
FROM Student AS s