formatting decimals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

formatting decimals

Author

Topic

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-06-25 : 06:49:17

In my data, I've got a decimal (18,3) field which sometimes stores whole numbers (100) and sometimes stores decimals (0.125, 2.5, etc). The values store just fine however I also write them along with other parts of the record to a "log" in a sentence format. An example would be 'Ordered 2.5 widgets on 12/31/2002' This is done as part of a trigger.

The problem I have is that all the values show the trailing zeros (100.000, 2.500) and I don't want that. How can I best (in T-SQL) examine the value and truncate so that the above example looks like 100 and 2.5?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-06-25 : 07:53:37

Hi!

This might not be the most optimized way, but it is A way

It's a UDF called r_test. It shaves trailing zeroes after the decimal point and returns a string.

Regards,
Kalle Dahlberg

CREATE FUNCTION r_test (@num numeric(18,3))

RETURNS varchar(100)

AS

BEGIN
declare @return_val varchar(100)
declare @tmp varchar(1)

select @return_val=convert(varchar(100), @num)

select @tmp=right(@return_val, 1)

While @tmp = '0'
Begin
select @return_val = left(@return_val, len(@return_val)-1)
select @tmp=right(@return_val, 1)
End

if right(@return_val, 1) = '.'
select @return_val = left(@return_val, len(@return_val)-1)

return @return_val
END

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 08:00:07

Is that not assuming he is SQL 2000?

Dan

<<monet makes money>>

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-06-25 : 08:04:34

That's an option, however I'm currently on SQL7. Will be going to 2000 in a few months. I could incorporate the code into the trigger which will produce the wanted output. Unless someeone suggests an easier way, I'll do it. Thanks.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-25 : 08:34:34

If it's only got 3 DP, can you not just cast it to float and then to varchar? Perhaps not, this doesn't retain the fractional part if the number gets large (and it starts using scientific notation).

Alternatively, this should work for any decimal where the scale > 0:
REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(CAST(@number AS varchar(100)), '0', ' ')),' ','0'),'.',' ')),' ','.')

Edited by - Arnold Fribble on 06/25/2002 08:41:51

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 08:50:30

A bit ugly but should keep pre SQL 2000 users happy...

declare @p decimal (18,3)

set @p = 22.330

select (case when right(convert(varchar(20),@p),4) = '.000'
then substring(convert(varchar(20),@p),1, charindex(".",convert(varchar(20),@p),1) - 1)
when right(convert(varchar(20),@p),2) = '00'
then substring(convert(varchar(20),@p),1, charindex(".",convert(varchar(20),@p),1) + 1)
else substring(convert(varchar(20),@p),1, charindex(".",convert(varchar(20),@p),1) + 2)
end) as ZeroesTruncated

-- result is '22.33'

set @p = 22.300
-- result is '22.3'

set @p = 22.000
-- result is '22'

set @p = 22
-- result is '22'

set @p = 22.303
-- result is '22.303'

HTH

Dan
www.danielsmall.com IT Factors

<<monet makes money>>

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-06-25 : 08:51:13

Sorry about assuming you had SQL2K

Man Arnold that's a nasty piece of code

but it works so it's fine :)

[edit]and the same to you Danny :)[/edit]

Edited by - andraax on 06/25/2002 08:52:22

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 08:54:22

A better one line version catering for all possible scenarios...

select (case when right(convert(varchar(20),@p),4) = '.000'
then substring(convert(varchar(20),@p),1, charindex(".",convert(varchar(20),@p),1) - 1)
when right(convert(varchar(20),@p),2) = '00'
then substring(convert(varchar(20),@p),1, charindex(".",convert(varchar(20),@p),1) + 1)
when right(convert(varchar(20),@p),1) = '0'
then substring(convert(varchar(20),@p),1, charindex(".",convert(varchar(20),@p),1) + 2)
else convert(varchar(20),@p)
end) as ZeroesTruncated

Assume you notional decimal length is < 21 chars

Dan

<<monet makes money>>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-25 : 09:05:58