Trim 0 from right side

Question

Hi I have value displayed 17125.000000 in sql query . I want to remove 3 zeros from right side It should display 17125.000 Thanks

Amit Mohanty · Answer

If your value is varchar then: DECLARE @yourValue VARCHAR(20) = '17125.000000'; SELECT LEFT(@yourValue, LEN(@yourValue) - 3) AS Result;

Amit Mohanty · Answer

SELECT CAST(17125.000000 AS DECIMAL(18, 3)) AS Result; You can also use FORMAT : SELECT FORMAT(17125.020000, '#.000') AS Result

Jignesh Kumar · Answer

You can convert it into Varchar(20) from that value as below, SELECT LEFT(CAST(17125.123000 AS VARCHAR), CHARINDEX('.', CAST(17125.000000 AS VARCHAR)) + 3) AS result; SELECT CONVERT(VARCHAR(20),CONVERT(DECIMAL(18,3),17125.000000))

Jayraj Chhaya · Answer

To remove trailing zeros from a value in SQL, you can use the TRIM function along with the 0 character. Here's an example: SELECT TRIM(TRAILING '0' FROM '17125.000000') AS trimmed_value; In this example, the TRIM function is used to remove the trailing zeros from the value '17125.000000' . The result will be '17125' . If you have a column in a table that contains the value '17125.000000' , you can use the same approach: SELECT TRIM(TRAILING '0' FROM column_name) AS trimmed_value FROM table_name; Replace column_name with the actual name of the column and table_name with the actual name of the table. By using the TRIM function with the TRAILING keyword, you can remove trailing characters from a string. In this case, we are removing the trailing zeros.

Ramco Ramco · Answer

Hi Jignesh I am converting this value as varchar . From this i want to remove 3 decimals from right. Thanks

Jignesh Kumar · Answer

Hello Ramco, Please use this query, SELECT CONVERT(DECIMAL(18,3),17125.000000) SELECT CONVERT(DECIMAL(18,3),YourColumnName) from YourTableName

Trim 0 from right side

Answers (6)