Trim right of decimal in varchar field

Question

Post reply

Trim right of decimal in varchar field

T_Peters

Hall of Fame

Points: 3052
More actions
April 14, 2008 at 3:22 pm

#379802

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

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

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

T_Peters Hall of Fame Points: 3052 More actions · Answer 1

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

Sergiy SSC Guru Points: 110209 More actions · Answer 2

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

RyanRandall SSChampion Points: 13623 More actions · Answer 3

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.

Sergiy SSC Guru Points: 110209 More actions · Answer 4

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