SQL Server Filter by Version

Question

Alright, say I want to filter a table which has stored version numbers in X.X.X format.

This is very easy if I just want to filter a single version number.

where version = '3.12.92'

as an example.

But what if I am looking for a certain RANGE of versions, such as from '2.21.23' to '3.12.92?' What would be the least complicated but reliable solution for filtering by a version range?

EDIT: It is too late to change the formatting of the table. The table is years old with MILLIONS of entries.

possible duplicate of [How to compare software versions using SQL Server?](http://stackoverflow.com/questions/11364242/how-to-compare-software-versions-using-sql-server) — Cᴏʀʏ, Apr 15 '15 at 13:54
the real solution is to fix your table design, then your search would be trivial. NEVER combine multiple values in a single column, you will forever have problems splitting them apart. If you had each segment of your version in a different column you could easily do this. — KM., Apr 15 '15 at 13:56
Too late for that, the table I am searching has millions of entries. — 11clock, Apr 15 '15 at 13:57
Are all version strings always in the format `x.y.z` (three parts, two decimals)? — Cᴏʀʏ, Apr 15 '15 at 13:59
best short term bet is to get a temp table/derived table/table variable/cte that contains every version in your range and do a join. Long term, I'd look to a presisted indexed computed column where you can encode these in a int or big int, where you use leading zeros and put an index on it. like: "3.12.92" becomes: "300120092" and 1.2.3 becomes "100020003" — KM., Apr 15 '15 at 14:00
Yes they are all in that format (but there can be two parts one decimal ones). — 11clock, Apr 15 '15 at 14:00
is the format always x.xx.xx ? or would 3.01.01 stored as 3.1.1 ? — A ツ, Apr 15 '15 at 14:18

score 0 · Answer 1 · answered Apr 15 '15 at 14:03

0

if there are always three at most two digit numbers separated by dots you can write a function to convert x.y.z into an integer of the form x*10000 + y*100 + z.

answered Apr 15 '15 at 14:03

1010

1,779
17
27

score 0 · Accepted Answer · edited Apr 15 '15 at 14:26

The solution would be to separate the version bits into separate columns for a quick fix you can use the following query

DECLARE @version VARCHAR(100) = '3.12.92';

SELECT  REVERSE(PARSENAME(REVERSE(@version) , 1)) AS FirstBit
       ,REVERSE(PARSENAME(REVERSE(@version) , 2)) AS MiddleBit
       ,REVERSE(PARSENAME(REVERSE(@version) , 3)) AS LastBit

Result:

FirstBit    MiddleBit    LastBit
   3           12          92

Your Where clause for where first bit = 3 would look something like....

WHERE REVERSE(PARSENAME(REVERSE(@version) , 1))  = '3'

SQL Server Filter by Version

2 Answers2