October 10, 2002 at 4:10 pm
I have a varchar column Version that contains data in the form of 5.6.667
I want to find all columns with a Version less that 6.6.773. How do I do the compare?
If I do it as a string, it only compares up until the first dot.
October 10, 2002 at 4:29 pm
Ok I will assume 6 is you major, .6 is your minor, and .667 is you revision. Try this.
If minor has to be only 1 character in size and revision 3 even if 000 then
WHERE REPLACE(verval,'.','') < 66773
should do the trick.
If the lengths in the positions are not fixed then you will have to use CHARINDEX to find the first '.' and the SUBSTRING and REVERSE with CHARINDEX and LEN to find the second '.' then determine that the lengths will match so tyou can perform the above type where or in the code force the lengths. Sorry, no test server here so I cannot put it together, but maybe someone else can or you can get an idea from this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 10, 2002 at 6:31 pm
Antares has the best solution.
Steve Jones
October 11, 2002 at 10:38 am
What if the version can be any length
ie - xx.xx.xxx or xx.xx.xxx.xx
October 11, 2002 at 11:20 am
Then like I said, you need to parse them and put them together in a format that will make versions the same lenght in proper notation order before you try to compare. Can you give me an idea of all the possible formats and how the notation positions identify, I may can script something to care for each case. I suggest in the future try to make sure you version with the same number of poisitions and lengths for easiest compare but looks like maybe you have some VB and C++ programs and thus the extra .xxx on the end.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 11, 2002 at 3:23 pm
specifically, the version will be Mac OS versions. I am not sure if they will always be in a fixed format or not. The data I do have looks like this. 10.1.3 and 10.1.5. IF this were the case, it would be easy. If not, I know that I have to somehow lop of each section and compare. I know I would use charindex and substing to do this I just cant seem to wrap my brain around it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply