CODEWITHSUNDEEP
mysqlsqlsql-server
hippo
hippo

Reputation: 19

SQL: Manipulate Datatypes - Float to String

I am trying to run a simple report where one of the columns is a float type, but I want to change the result to show a string (I think that is the best way). If the value of the float column is 101 or 201, I want the result to show as '101, 201' and other values to show as they are in the table.

This is what I've been messing with, but the results are incorrect:

SELECT float_column, 
CASE 
    WHEN (STR(float_column, 10, 16)) IN ('101', '201') THEN '101,201'
    ELSE column
END AS float_column, column2, column3
FROM database.table
ORDER BY float_column

Upvotes: 0

Views: 976

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Why wouldn't you use number comparisons?

SELECT float_column, 
       CASE WHEN float_column in (101, 201) THEN '101,201'
            ELSE CAST(float_column as VARCHAR(255))
       END AS string_column, column2, column3
FROM database.table
ORDER BY float_column;

I don't usually advise equality comparisons for floats -- but the above could be adjusted by converting to an integer to numeric. I don't see what the conversion to the string is doing for you.

Upvotes: 1

Related Questions