Remove trailing zeros

[kafi]

Hi;

I want to remove trailing zeros from the results produced by stored procedure.
SELECT rdts_SizeIN
FROM CatalogSQL.dbo.RuptureDiskTypeSize
WHERE [rdts_RuptureDiskTypeID]=@rdts_RuptureDiskTypeID and rdts_Active
=@Active

rdts_SizeIN is a decimal field in the table.

Resulting data maybe:
0.2500
0.5000
1.0000
1.5000
2.0000
3.0000
I want it to look like:
0.25
0.5
1
1.5
2
3

Can some one help

[Jens]

I once write a function for this:

CREATE FUNCTION dbo.fn_removetrailingchars
(
@strValue VARCHAR(200),
@TrailingChar VARCHAR(200),
@RemoveLeading BIT
)
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @intCount int
SET @intCount = 0

WHILE @intCount <= LEN(@strValue)
BEGIN
SET @intCount = @intCount +1
IF SUBSTRING(@strValue, @intCount, 1) NOT LIKE @TrailingChar
BREAK
ELSE
CONTINUE
END
IF @RemoveLeading = 1
SET @strValue =
REVERSE(dbo.fn_removetrailingchars_drkw(REVERSE(RIGHT(@strValue,
LEN(@strValue) - @intCount +1 )),@TrailingChar,0))
ELSE
SET @strValue = RIGHT(@strValue, LEN(@strValue) - @intCount +1
)

RETURN @strValue
END

Lets me know if that helps,

HTH, jens Suessmeyer.

[ML]

Formatting should be done on the cilent. You could convert the values to
varchar and format them, but then those wouldn't be numbers any more.

ML

---
http://milambda.blogspot.com/

[William Stacey [MVP]]

Why can't you format that on the client?

--
William Stacey [MVP]

"kafi" <ka...@discussions.microsoft.com> wrote in message
news:E24D4944-9DD3-4053...@microsoft.com...

[SQL]

Interesting, in 1 select staement it would be like this
create table testdecimals(testdata decimal(20,5))
insert into testdecimals
select 0.2500 union all
select 0.5000 union all
select 1.0000 union all
select 1.5000 union all
select 2.0000 union all
select 3.0000

select
replace(rtrim(replace(replace(rtrim(replace(convert(varchar,testdata),'0','
')),' ','0'),'.',' ')),' ','.')
from testdecimals

probably better ways out there from some of the MVP's

http://sqlservercode.blogspot.com/

[kafi]

I am using this SP multiple places with different controls etc. In other
words if I do the formating in SP that means it will be done only one place,
otherwise have to do it with as many places I am using it and binding the SP
results.

[Alexander Kuznetsov]

select replace(rtrim(replace('10305.909000','0',' ')),' ','0')

[William Stacey [MVP]]

Right but you normally want to keep it as a number anyway and only format it
as the last step in a display. Then you have the option of calcs on the
client and other without always converting between string and numeric and
visa-versa.

--
William Stacey [MVP]

"kafi" <ka...@discussions.microsoft.com> wrote in message

news:1D08BCD9-635A-46D7...@microsoft.com...

[SQL]

Alexander,

you will need to do another replace for the decimal point at the end
instead of this
select replace(rtrim(replace('10305.00000','0',' ')),' ','0')
it has to be this
select
replace(rtrim(replace(replace(rtrim(replace(convert(varchar,'10305.00000'),'0','

')),' ','0'),'.',' ')),' ','.')

http://sqlservercode.blogspot.com/

[Alexander Kuznetsov]

SQL, I agree