How to Remove Trailing Zeros from SQL Server Decimal Values?

Eliminating Trailing Zeros in SQL Server Decimal Data

Challenge:

Storing decimal values in a DECIMAL(9,6) column often results in unwanted trailing zeros. For example, inserting 123.4567 yields 123.456700.

Solution:

The presence of trailing zeros in decimals is primarily a display issue, usually handled by the application or client. SQL Server Management Studio (SSMS), however, displays floating-point numbers without these trailing zeros. We can exploit this behavior to remove them.

The solution involves a two-step cast: first to DECIMAL, then to FLOAT:

<code class="language-sql">SELECT 
    CAST(123.4567 AS DECIMAL(9,6)) AS DecimalValue,
    CAST(CAST(123.4567 AS DECIMAL(9,6)) AS FLOAT) AS FloatedValue;</code>

This produces:

<code>DecimalValue        FloatedValue
123.456700            123.4567</code>

Casting to FLOAT effectively removes the trailing zeros from the displayed value, providing a simple and efficient workaround for scenarios where their presence is problematic.

The above is the detailed content of How to Remove Trailing Zeros from SQL Server Decimal Values?. For more information, please follow other related articles on the PHP Chinese website!