Trim right of decimal in varchar field

  • Greetings,

    I'm working on a some formatting to match invoice reference fields. One of the requests is to drop the characters to the right of the right most decimal. For example...

    Original Formatted

    BMT3186.1 BMT3186

    BW193179.1 BW193179

    A41.Q1.8 A41.Q1

    Has anyone had to do this kind of operation before and can you offer any suggestions?

    Thanks,

    Tim

  • OK, this is a first for me, I was able to answer my own post 😛

    LEFT(Reference, LEN(Reference) - PATINDEX('%[.]%', REVERSE(Reference)))

    Unless, of course, someone points out where this goes wrong. Haven't found it yet.

    Thanks,

    Tim

  • Tim Peters (4/14/2008)


    OK, this is a first for me, I was able to answer my own post 😛

    LEFT(Reference, LEN(Reference) - PATINDEX('%[.]%', REVERSE(Reference)))

    Unless, of course, someone points out where this goes wrong. Haven't found it yet.

    Thanks,

    Tim

    This will go wrong for values with trailing spaces. 🙂

    _____________
    Code for TallyGenerator

  • This will go wrong for values with trailing spaces.

    Using DATALENGTH rather then LEN will fix this 🙂

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • RyanRandall (4/14/2008)


    This will go wrong for values with trailing spaces.

    Using DATALENGTH rather then LEN will fix this 🙂

    Datalength depends on datatype - varchar or nvarchar.

    You need to convert value to certain datatype before proceeding.

    REVERSE(RTRIM(Reference)) is much simpler. 🙂

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply