SQL Server: How do I sort and compare lots of versions?

Question

In my request to a table I have to find all the lines with versions where versions are less or equal to 10.0.1703 (<= 10.0.1703) and there is plenty of versions there. I use SQL Server. Example of the lines needed in result is:

id | name | build(version)
--------------------------
1  | A    | 10.0.1703
2  | B    | 6.3.9600

I have to find all versions older or equal to 10.0.1703 . 6.3.9600 is less than 10.0.1703

It is connected with this question, but the solutions offered are hard to understand for me How to compare software versions using SQL Server?

Can you post some sample data and expected results please? Also, one of those links is for MySQL, which is a completely different RDBMS to SQL Server; which are you actually using? Ideally, however, if you're storing version numbers in data, it's best to store them in different parts rather than 1. I.e. have 4 columns such as `MajorVersionNumber`, `MinorVersionNumber`, `BuildNumber`,`RevisionNumber`. — Thom A, Feb 25 '19 at 10:41
(i added that to description of question)I use SQL Server. Example of the lines needed in result is enter image description here id name build(version) 1 A 10.0.1703 2 B 6.3.9600 I have to find all versions older or equal to 10.0.1703 . 6.3.9600 is less than 10.0.1703 — smpr, Feb 25 '19 at 10:56
OK, now we just need confirmation of the RDBMS you're using. I don't want to give a T-SQL answer and then find out your using MySQL. If you run `PRINT @@VERSION;` what is returned? — Thom A, Feb 25 '19 at 11:01
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) — smpr, Feb 25 '19 at 11:04

score 5 · Answer 1 · answered Feb 25 '19 at 11:01

5

one way would be to cast as hierarchyid

WHERE CAST('/' + build + '/' AS HIERARCHYID) <= CAST('/10.0.1703/' AS HIERARCHYID)

DEMO

answered Feb 25 '19 at 11:01

Martin Smith

438,706
87
741
845

Thank you, this quiery works but misses a lot of lines with builds(versions) like 10.0.14393 , 5.2.3790, 10.0.15063 etc. – smpr Feb 25 '19 at 11:41
2

@smpr - it returns `5.2.3790` (demo https://rextester.com/CAIKQ32951) and it is correct that the other two are missed. They are higher than `10.0.1703`. Both `14393` and `15063` are larger than `1703` so they have equal major and minor versions and later patch versions – Martin Smith Feb 25 '19 at 12:03

score 0 · Answer 2 · answered Feb 25 '19 at 12:19

I agree with @Larnu, if you're going to be using this for comparisons as opposed to just display, you are way better off splitting up these values and storing them separately.

But given what you have, in either case you'd still have to have some logic to determine bigger or smaller. I like PARSENAME() for this, then you can build out a numeric value for the version similar to how you would for an IP address.

Given this table and data:

CREATE TABLE #x(id int NOT NULL, name char(1), build varchar(32));    

INSERT #x VALUES(1,'A','10.0.1703'),(2,'B','6.3.9600');

We can write a query like this (nested CTEs only to avoid running PARSENAME() logic more than once):

DECLARE @criteria varchar(32) = '10.0.1703';

;WITH x AS
(
  SELECT id, name, build FROM #x
  UNION ALL 
  SELECT id = NULL, name = NULL, build = @criteria
),
y AS
(
  SELECT id, name, build,
    bn = PARSENAME(build,3)*10000000 + PARSENAME(build,2)*100000 + PARSENAME(build,1)
  FROM x
)
SELECT id, name, build FROM y 
  WHERE bn < (SELECT bn FROM y WHERE id IS NULL);

This assumes:

all your builds come from the same product, and so
- always contain the same number of "parts"
- don't ever contain characters
10.0.14393 > 10.0.1703

SQL Server: How do I sort and compare lots of versions?

2 Answers2