The problem of querying 4-part version numbers has long been a problem. I have seen many solutions, but none are 100% fool proof, until now. But what is a 4-part version numbers? This article will go into details to explain 4-part version numbers and how SQL Server queries them. In the next weeks article, I will show you how to query any part of the version numbers and allow you to use greater thon and less than operators.
What is a query 4-part version numbers?
Let us step back and make sure we are all on the same page as to what a four-part version number is. The problem is that most software will use major version, minor version, revision number and build number. Using Google Chrome for my example. The major version is 108, whereas the minor version is 0 and revision number is 5359 and the build is 125. Everyone think this is a number, when in fact it is a string and therefore should be treated as such. It should be noted that a 4-part version number is also referred to as Octet. In this case 3rd octet is 5359.
ConfigMgr ARP Table
Just to confirm, using SQL Server Management Studio (SSMS) looking at Add Remove Programs 64 (ARP) table within ConfigMgr. We can see that the column is a nvarchar with a size of 255 characters. e.g. a string.
Now let us go future and review our sample data set. Notice that we have two computers with 15.0.984.2 and one with 15.0.18424.0. In the example above which one is greater? We will all say the first one, right? But if they are both strings, is it really bigger?? Isn’t the 9 in 984 greater then the 1 in 18424? Yes, it is, therefore when doing a string compare 15.0.984.2 is bigger then 15.0.18424.0.
I know some of you are doubtful but let’s demonstrate this within SQL. Also see the video below.
In the query above, you can see that I am looking for all computers with a version greater than or equal to 15.0.984.2. Also notice within the results that the 18424 computer is NOT listed. The video shows this a bit better. This is because 15.0.9 is greater than 15.0.1
Ok, I can hear some of you saying Garth you have done it wrong you should be looking for 18424! So above you can see that I have done exactly that and again you can see that this is NOT what many of you expect. Again this is because 15.0.9 is greater than 15.0.1.
Cast and Convert
I have also heard that you can use cast and convert to convert a four-part version number to a true number but this is not the case. As you can see above you will get error when you run your SQL query.
How to query 4-part version numbers ?
In this video, I will go over the step listed above so that you can see the whole processes in action.
Hopefully this help clear up four part version strings being believed to be version numbers. Don’t get me wrong I still call them version numbers but I know that they are strings.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, if you have any questions, please feel free to touch base @Garthmj.