SQL select string and turn it into a decimal

[maxutil]

select
    col as original,
    col::double as val_double,
    col::float as val_float,
    col::decimal(10,4) as val_decimal,
    to_number(col, '99999.99999') as val_tonum
from int_fx_conversion_rate;

The original value of col is a string such as '1.2345' out to 4 decimal places. When I run this above, all of the casted versions come out rounded to 2 decimal places (1.23). How do I get all the decimals?

[Anonymous · Accepted solution]

@Chris Chung​ : The issue you're experiencing is likely due to the default rounding behavior in SQL. To preserve all the decimal places, you can modify your query to cast the column as a string. Example: CAST(col AS STRING) AS col_string. Please try this.

[Anonymous]

Hi @Chris Chung​ 

Thank you for your question! To assist you better, please take a moment to review the answer and let me know if it best fits your needs.

Please help us select the best solution by clicking on "Select As Best" if it does.

Your feedback will help us ensure that we are providing the best possible service to you.

Thank you!