Converting VARCHAR to floating decimal in SQL?

Question

I’m trying to convert a “string” column into all float values and can’t seem to get the correct output. The STRING values are all in #.## format (eg. 2.33, 4.25, 3.75, etc.) I have a query that looks to add a new column based on two conditions, one of which conditions requires the conversion:

SELECT 
    time, score, 
    CASE 
        WHEN time >= 3 AND CAST(score AS DECIMAL) >= 3 
            THEN ‘Yes’ 
            ELSE ‘No’ 
    END AS Recommendation
FROM
    table1

My output, however, isn’t capturing the correct result:

Time	Score	Recommendation
4	1.2	Yes
5	2.1	Yes
2	4.1	No

In this case, I would expect the first two rows to be “No”, since the score that we converted to a float is less than 3.00.

Answer 1

Try :

SELECT time, score, 
       CASE 
          WHEN time >= 3 
               AND CAST(REPLACE(score, ',', '.') AS DECIMAL(16,2)) >= 3.0 
             THEN 'Yes' 
          ELSE 'No' 
       END AS Recommendation
FROM   table1