April 14, 2008 at 3:22 pm
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
April 14, 2008 at 3:46 pm
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
April 14, 2008 at 4:12 pm
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
April 14, 2008 at 4:24 pm
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.
April 14, 2008 at 4:35 pm
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