Suppress Trailing Zeros?

Question

Post reply

Suppress Trailing Zeros?

Viewing 11 posts - 16 through 25 (of 25 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

How about

replace(rtrim(replace(column,'0',' ')),' ','0')

Far away is close at hand in the images of elsewhere.
Anon.

GregLyon SSCommitted Points: 1946 More actions · Answer 2

Thanks again Antares, and Thanks David.

I combined these two ideas to get:

SELECT

REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(ADJ_VALUE,'0',' ')),' ','0'),'.',' ')),' ','.') As Adj_Val

This single line of code does exactly what I want! The first -replace(rtrim(replace- trims the decimal point off of whole numbers and the second -replace(rtrim(replace- trims trailing zeros. Very nice.

It's interesting to me that this solution does not require explicit conversion of the number to a Varchar or Char. All of the other solutions do require explicit conversion. I don't understand exactly why that is, or if I SHOULD do the conversion anyway...Any thoughts?

I tried it:

REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(CONVERT(Varchar(19),ADJ_VALUE),'0',' ')),' ','0'),'.',' ')),' ','.') AS ADJ_VAL

It doesn't seem to make any difference performance wise. In fact as far as performance is concerned, all of the methods in this thread perform very similarly.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

In my case, I'm hitting against 58 million rows with 2 joins, and antares686 second script with some slight mods to adjust for my case of 2 zeros, and Greg's last post that he used, again with slight mods for my 2 zeros gives the best performance by roughly 8 minutes, in case anyone is interested. I'm converting my case statement solution already.

I ALWAYS use explicit conversion over implicit conversion as a given. I've had situations where implicit conversions have given me unexpected results before, and made it a habit to do my own conversions rather than rely on the system to do it for me.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Intersting script. Not sure I'd do it that way, but it's a great solution.

2nd vote also for explicit conversions. Better to do it yourself and be sure you don't get something weird.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

Antares686 SSC Guru Points: 125444 More actions · Answer 5

I too favor explicit instead of implicit. I ran into an issue with differences between SQL 7 and 2000 on this very issue and decided it is far easier to have code upgrade by explicit conversion in use than the other way.

GregLyon SSCommitted Points: 1946 More actions · Answer 6

Thanks again to all of you. I was leaning toward explicit declaration anyway, but with this strong, unanimous endorsement I'll do it for sure. I always harp on it for programming so it makes sense to do it with SQL too.

Happy New year,

Greg.

tcartwright SSCertifiable Points: 5573 More actions · Answer 7

Why would casting to a different datatype not work for you? A simple cast to the float datatype seems to work almost exactly as you wanted it to in your original post.

This :

DECLARE @num numeric(18, 10)

SET @num = 0.2000000000

PRINT @num

PRINT CAST(@num As float)

SET @num = 3.0000000000

PRINT @num

PRINT CAST(@num As float)

SET @num = 100.0000000000

PRINT @num

PRINT CAST(@num As float)

SET @num = 0.0016500000

PRINT @num

PRINT CAST(@num As float)

Produces :

0.2000000000

0.2

3.0000000000

3

100.0000000000

100

0.0016500000

0.00165

Tim C.

//Will write code for food

Tim C //Will code for food

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Actually running the cast to float came out with these results

0.20000000000000001

3.0

100.0

0.00165

Works fine in some cases but not all.

It is due to it's nature that this occurrs.

Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

GregLyon SSCommitted Points: 1946 More actions · Answer 9

FLOAT also gets ugly, both in output and in accuracy, with really small numbers:

Declare @t as Decimal (18,12)

set @t = .0000000009

SELECT cast (@t as float)

Result:

-----------------------------------------------------

8.9999999999999999E-10

(1 row(s) affected)

Also as Antares pointed out, FLOAT is not accurate, and rounding errors can be introduced. The numbers I'm working with are exact and must be represented as such in the reports.

GregLyon SSCommitted Points: 1946 More actions · Answer 10

Another oddity. How you view the results affects the test.

Try using PRINT vs SELECT to view the output when casting as FLOAT:

Declare @val Decimal(18,10)

SET @val = .2000000000

PRINT Cast(@val AS FLOAT)

SELECT CAST(@val AS FLOAT)

Results (in isqlw):

0.2000000000

0.2

-------------------

0.20000000000000001

lambje SSCrazy Points: 2085 More actions · Answer 11

Sweet. I think David's solution is a real winner. I'd also vote for explicit conversions. When I tried out David's code I did that out of habit. I wouldn't have even thought it would have handled this implicitly. Interesting.