remove trailing zeros

Question

remove trailing zeros

dobrzak-134971

Right there with Babe

Points: 737
More actions
June 12, 2006 at 8:00 am

#190830

Hi All,
there is any function (or something else) which remove zeros from number, ie:
134.000 -> 134
1.1200 -> 1.12
0.0200 -> 0.02
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rodjkidd SSCoach Points: 15915 More actions · Answer 1

Dear Dobrzak,

I am sure you have a really good reason for this, as if the numbers are stored as decimal or money then the trailing zeros wouldn't matter anyway, to how they are stored. As for formating it should really be down to your client app/ report writer. But if you really want to do this on SQL Server and the numbers are stored at characters then this would work:

select REVERSE(SUBSTRING(reverse

(@Num),

PATINDEX('%[1-9]%', reverse(@Num))

, len(@Num) - PATINDEX('%[1-9]%'

, reverse(@Num)) + 1))

You could always turn it into a function.

Cheers,

Rodney.

dobrzak-134971 Right there with Babe Points: 737 More actions · Answer 2

Hi,

thanks for reply and help. I have a really good reason However this script doesn't work (ie. 100.0000). If doesn't exis any function for do this, I change a little your script

Thanks again.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

If data are stored as floating point, use

declare @num table (i float)

insert @num

select 134.000 union all

select 1.1200 union all

select 100.00 union all

select 0.0200

select i,

convert(varchar, i) converted

from @num

If data are stored as string, use

declare @num2 table (i varchar(50))

insert @num2

select '134.000' union all

select '1.1200' union all

select '100.00' union all

select '0.0200'

select i,

convert(varchar, convert(float, i)) converted

from @num2

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 4

Very interesting... never had to do such a thing before but how simple can you get? Thanks, Peter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rodjkidd SSCoach Points: 15915 More actions · Answer 5

Hi Peter,

I had looked at using decimal and money - didn't think to use float - tend to avoid it if I can! Nice solution.

Pity I missed what happens with 100.000 with my solution. Tried a few other decimal numbers, and it worked OK. Testing was never my strong point - I always give the code to somebody else to test!

I was thinking if there were instances where this kind of problem would come up. But other than some really weird right padding with a character or number that already exists in the column I couldn't think of any.

Cheers,

Rodney.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Well, sometimes life is easy.

BTW, there is a six digit limit for the conversion as stated in BOL

This table shows the style values for float or real conversion to character data.

Value	Output
0 (default)	Six digits maximum. Use in scientific notation, when appropriate.
1	Always eight digits. Always use in scientific notation.
2	Always 16 digits. Always use in scientific notation.

So here is a complete "ordinary style" solution.

declare @num3 table (i varchar(50))

insert @num3

select '134.000' union all

select '1.1200' union all

select '100.00' union all

select '69' union all

select '13.' union all

select '123456789.9876543210000000000000000000000000' union all

select '0.0200'

SELECT i,

CASE WHEN PATINDEX('%[1-9]%', REVERSE(i)) < PATINDEX('%.%', REVERSE(i)) THEN LEFT(i, LEN(i) - PATINDEX('%[1-9]%', REVERSE(i)) + 1) ELSE LEFT(i, LEN(i) - PATINDEX('%.%', REVERSE(i))) END 'Converted'

FROM @num3

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 7

Yep... knew that about the conversion and SN... like others, I normally avoid float because of it's binary limitations on accuracy. Because I avoid it, I didn't even think that the simple conversion to varchar on small numbers would drop trailing zeros appropriately.

I do appreciate the reminder on the 6 digit thingy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)