• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1674
  • Last Modified:

user function in sybase

Hi,

we are using sybase 11.5 Ver. we have a req.
in which we have to expose one of numeric data  with turnctaed trailing zero's.

for eg: If we have 0.87900000 . This should be displayed as 0.879
0.88756000 as 0.88756 and 0.88000000 as 0.88

Pls let us know how we can go ahead.

Thanks

Raj
0
ayen
Asked:
ayen
  • 2
  • 2
2 Solutions
 
bretCommented:
The following seems to do the trick on 15.0 -


select reverse(reverse(convert(varchar(10), mycolumn))) from mytable

but it may not be supportable (I'm not sure why convert() drops the trailing zeros).

I'd be much more  confident that code like the following would be robust (the variables can
be eliminated and everything combined on one line) to handle multiple rows:

create table foo (x float)
go
insert foo values (0.879000)
declare @x varchar(10)
declare @y int
select @x = reverse(convert(varchar(10),x)) from foo
select @x
select @y = patindex("%[^0]%", @x)
select @y
select reverse(substring(@x,@y, 10))

(i.e convert value to a string, flip it, find the offset of the first non-zero value, substring out the remainder, flip that back to get the final value).
0
 
ChrisKingCommented:
why not just convert to a float then to a string

see the following of a float versus a numeric

1> select convert(varchar,convert(numeric(12,6),1234.5670))
2> select convert(varchar,convert(float,1234.5670))
3> go

 ------------------------------
 1234.567000

(1 row affected)

 ------------------------------
 1234.567

(1 row affected)
0
 
bretCommented:
My concern there is that I don't think the behavior leaving off trailing
zeroes is documented - and so could possibly change.  While simply
converting to a string currently works, wouldn't it be better to have
more bulletproof code?

-bret
0
 
ChrisKingCommented:
well you would know that sort of thing more than us
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now