1

I need to trim trailing '0's from numbers such as 0.50, 0.40 etc. Any suggestions? Unable to use TRIM function in SQL Server.

I don't want to use convert(decimal(10, 1), number) since that will affect values such as '0.23' for ex.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mish
  • 35
  • 6
  • 1
    This is something which is usually handled in your presentation layer. I attempted an answer below, which covers your immediate question. – Tim Biegeleisen Mar 02 '17 at 06:41

2 Answers2

0

Here is a generic approach which should remove all trailing zeroes from any number. First, we cast the number to text, and count how many trailing zeroes are there, using REVERSE(), REPLACE(), and CHARINDEX(). Then, we use LEFT() to take the entire number text minus how many trailing zeroes were found.

SELECT LEFT(CAST(col AS VARCHAR),
            LEN(CAST(col AS VARCHAR)) -
            CHARINDEX(LEFT(REPLACE(REVERSE(CAST(col AS VARCHAR)), '0', ''), 1),
                      REVERSE(CAST(col AS VARCHAR))) +
            CHARINDEX(LEFT(REPLACE(REVERSE(CAST(col AS VARCHAR)), '0', ''), 1),
                      REPLACE(REVERSE(CAST(col AS VARCHAR)), '0', ''))) AS new_col
FROM yourTable

Demo here:

Rextester

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

U can try this script :

select convert (DOUBLE PRECISION , column_name) from table_name

Jacky Montevirgen
  • 307
  • 2
  • 14
Nischey D
  • 189
  • 3
  • 18