Drop trailing zeros from decimal(18,6) field 1

[cmhunt]

Hi

I've got a column which is a decimal(18,6) which contains numbers with varying numbers of decimal places. Couple of examples:
0.00025
0.0005
300.
Stored as they are, when I write a SELECT statement, the numbers are output as 0.000250, 0.000500, 300.000000 respectively.

I require a way of dropping the trailing zeros after the decimal places, outputting the numbers which went in. Is there a function to do this?? Using ROUND() will affect the values so I can't use that. Any other suggestions of ways of doing this would be gratefully appreciated.

Thanks

Chris

 

[john76]

The only way that I can think of is writing some sort of function or stored procedure that would convert the value to a string, then test for the trailing 0's and substring them out.

HTH,
John

 

[cmhunt]

Thanks - I had a horrible feeling someone may say that!! ;-)

If anyone already has a function handy.........


Thanks

Chris

 

[donutman]

Try:

SELECT Cast(Floor(YourNumber)as varchar(21))+'.'+
      Reverse(Cast(Floor(Reverse(YourNumber))as int))
   from YourTable

-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[cmhunt]

Superb!! Cheers!!!

Chris

 

[john76]

Nice donutman, star from me.

John

 

[donutman]

Test negatives, if you need to, to see if it works for you.
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Very cute trick Karl, I'll have to remember it.

 

[ESquared]

Oh, btw, you could just cast it as real, too...

 

[donutman]

Real doesn't work any better than float. The inaccuracy of the decimal datatype then shows up.
-Karl

 

[ESquared]

It annoys me that SQL server won't let you do % modulus on non-integer values. I've always used that in the past to return the decimal portion of a number, and now you've given me another way to do it. (Subtract the floor from the original value.)

It may not be any faster, but sometimes the thing you want the decimal part of is an expression, not a variable, so you have to either evaluate the expression twice or set a variable first. This gives me all-in-one-shot syntax again.

I'll have to test how it works, speedwise.

 

[cmhunt]

Karl

Had a play with that code last night and all seemed ok. When doing some proper testing this morning, I noticed that it works fine for anything with anything after the decimal place but with a value of say 300, it was outputting (as you'd expect) 300.0.

By putting a CASE in, I came up with

SELECT 
	CASE
		WHEN REVERSE(CAST(FLOOR(REVERSE(TickValue))AS int)) = 0
			THEN CAST(FLOOR(TickValue)AS varchar(21))
		WHEN REVERSE(CAST(FLOOR(REVERSE(TickValue))AS int)) <> 0
			THEN CAST(FLOOR(TickValue)AS varchar(21))+'.'+REVERSE(CAST(FLOOR(REVERSE(TickValue))AS int))
	END
FROM MPF

which works fine (although extremely ugly! I'll try to tidy up later). Thank you very much for your help.

Chris

 

[donutman]

I'm not at work, so I haven't tested this:

SELECT Cast(Floor(YourNumber)as varchar(21))+ 
      Case WHEN Floor(YourNumber)=YourNumber THEN '' 
         ELSE '.'+ Reverse(Cast(Floor(Reverse(YourNumber))
         as int)) END
   FROM MPF

-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[cmhunt]

Yep thanks, that works fine. Pretty much what I ended up with once I'd tidied mine up.

Thanks

Chris