topic Re: SQL select string and turn it into a decimal in Data Engineering

SQL select string and turn it into a decimal

maxutil — Mon, 27 Feb 2023 16:28:43 GMT

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?

Re: SQL select string and turn it into a decimal

Anonymous — Thu, 09 Mar 2023 01:26:49 GMT

@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.

Re: SQL select string and turn it into a decimal

Anonymous — Mon, 13 Mar 2023 05:02:02 GMT

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!