Remove trailing zeros

26 Jan 2006 5:07 PM

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

26 Jan 2006 5:19 PM

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/

26 Jan 2006 5:19 PM

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.

26 Jan 2006 5:20 PM

William Stacey [MVP]

Why can't you format that on the client?

--
William Stacey [MVP]

Show quote

"kafi" <k***@discussions.microsoft.com> wrote in message
news:E24D4944-9DD3-4053-813B-257F779B3F0D@microsoft.com...
| 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
|
|

26 Jan 2006 5:30 PM

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/

26 Jan 2006 6:16 PM

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.

Show quote

"William Stacey [MVP]" wrote:

> Why can't you format that on the client?
>
> --
> William Stacey [MVP]
>
> "kafi" <k***@discussions.microsoft.com> wrote in message
> news:E24D4944-9DD3-4053-813B-257F779B3F0D@microsoft.com...
> | 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
> |
> |
>
>
>

26 Jan 2006 7:00 PM

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]

Show quote

"kafi" <k***@discussions.microsoft.com> wrote in message
news:1D08BCD9-635A-46D7-BBDD-4BC733EF7D53@microsoft.com...
|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.
|
| "William Stacey [MVP]" wrote:
|
| > Why can't you format that on the client?
| >
| > --
| > William Stacey [MVP]
| >
| > "kafi" <k***@discussions.microsoft.com> wrote in message
| > news:E24D4944-9DD3-4053-813B-257F779B3F0D@microsoft.com...
| > | 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
| > |
| > |
| >
| >
| >

26 Jan 2006 6:46 PM

Alexander Kuznetsov

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

26 Jan 2006 7:04 PM

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/

26 Jan 2006 7:48 PM

Alexander Kuznetsov

SQL, I agree