What is a 4-part version numbers?

by | Jan 11, 2023 | SQL Server

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?

Chrome showing the 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

ARP table showing version as a nvarchar

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.

sample version numbers

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.

SQL query greater than 984

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

SQL query greater than 18424

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

Using SQL Cast on version column error

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.