user function in sybase

Posted on 2006-06-02
Last Modified: 2012-06-27

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.


Question by:ayen
    LVL 10

    Accepted Solution

    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)
    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).
    LVL 6

    Assisted Solution

    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


    (1 row affected)


    (1 row affected)
    LVL 10

    Expert Comment

    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?

    LVL 6

    Expert Comment

    well you would know that sort of thing more than us

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    DECT technology has become a popular standard for wireless voice communication. DECT devices are not likely to be affected by other electronic devices and signals because they operate in a separate frequency-band.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now