SQL: Manipulate Datatypes - Float to String

Question

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

MySQL or SQL Server? They are completely different. Probably you shouldn't be using a float in the first place - seems like you want an int. — Dale K
– Dale K, Commented Mar 30, 2021 at 23:01
What kind of strings will STR(float_column, 10, 16) produce? Have a look: learn.microsoft.com/en-us/sql/t-sql/functions/… — PM 77-1
– PM 77-1, Commented Mar 30, 2021 at 23:03
Just use numeric comparison and cast the output of all non 101,201 values — Chris Schaller
– Chris Schaller, Commented Mar 30, 2021 at 23:04

Chris Schaller · Accepted Answer · 2021-03-30 23:09:58Z

1

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.

edited Mar 30, 2021 at 23:09

Chris Schaller

17.1k3 gold badges57 silver badges101 bronze badges

answered Mar 30, 2021 at 23:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PM 77-1 Over a year ago

I understand that OP did it first, but it's not a good idea to use float column both as a field and alias.

Chris Schaller Over a year ago

@PM77-1 just fix it when you see stuff like that, together lets leave best practise examples for all

hippo Over a year ago

Thank you!! This really helped me in the right direction.

Collectives™ on Stack Overflow

SQL: Manipulate Datatypes - Float to String

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related